Search code examples
qlikviewqliksense

Unable to get comma separated values in Qlik text box for a certain logic

I want a comma separated value in a qliksense text box.

Consider the following data

REGION|MARKET|METRIC|VALUE

ASP|INDIA|S1|1

ASP|CHINA|S1|0

ASP|INDIA|S2|0

ASP|CHINA|S2|1

ASP|HONGK|S1|1

EUR|UK|S1|1

EUR|UK|S2|0

USA|TX|S1|1

USA|TX|S3|1

I want an output in a Qlik Sense text box with the following logic:

Sum of the VALUE column where VALUE=1 for metric S1.

Desired output in text box: ASP(2), EUR(1), USA(1)

I would really appreciate your help.

Solution

  • You can use this statement:

    Concat(Aggr(REGION & '(' & Sum({<METRIC={'S1'}>} VALUE) & ')', REGION), ',')

    Which will return you the desired result:

    enter image description here

    In case VALUE is not binary you can add VALUE = {1} to the set expression like:

    Concat(Aggr(REGION & '(' & Sum({<METRIC={'S1'}, VALUE = {1}>} VALUE) & ')', REGION), ',')

    Statement to load data:

    Data:
Load *
Inline [
REGION,MARKET,METRIC,VALUE
ASP,INDIA,S1,1
ASP,CHINA,S1,0
ASP,INDIA,S2,0
ASP,CHINA,S2,1
ASP,HONGK,S1,1
EUR,UK,S1,1
EUR,UK,S2,0
USA,TX,S1,1
USA,TX,S3,1
];