Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set analysis/wildmatch/SQL like statement

Hello,

i have 2 tables

County:

Country Code, Country Name, Region

ABC, A, R1

QWE, B, R1

POI, C, R1

ZXC, D, R2

BBB, E, R5

...

MainTable:

ID, Qty

2020_ABC_POI, 1

2020_ABC_ZXC, 2

2020_ZXC_BBB, 7

 

From the main table ID field contains year + 2 country code references separated by _

On the dashboard I am presenting users with a Region and Country Name filters. Based on country selection I am storing country codes in a variable. In a KPI object i want to sum Qty field based on my selections. For example:

Region selection = R1 --> sum should be 3

Region selection = R2 --> sum is 9

Country selection = A -> sum is 3

Country selection =  A and D --> sum is 10

My set analysis is the following: sum({<ID= {"*$(=$(variable))*"}>} Qty)

The variable concatenates Code field with , separator.

Issue with above is that it only works when i select a single country. I need the set analysis to take into account all Country Codes as per my selection

Thanks!

4 Replies
Highlighted
MVP
MVP

Why don't you split the ID column in main table in Year & Country code and link on Country code with Country table?

Contributor III
Contributor III

Those 2 tables come from my data warehouse. Country = dimension, which contains regions. Main table = fact. The ID column is a business key. Tables are joined by country_sk

i don’t want to deviate from that design and further denormalize the fact

 

Highlighted
Contributor III
Contributor III

I'm not sure if it works but could you try to concat the variable using separator

*", "*

or

*), $(*  

so the final variable result of the filter statement represents correct syntax for multiple matches?

Highlighted
Specialist
Specialist

Hi, @markobanjanin .

 

Please, share content of your variable.