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
    ];