topic Re: Set analysis/wildmatch/SQL like statement in App Development

Set analysis/wildmatch/SQL like statement

markobanjanin — Tue, 04 Aug 2020 15:12:02 GMT

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!

Re: Set analysis/wildmatch/SQL like statement

Kushal_Chawda — Tue, 04 Aug 2020 15:31:09 GMT

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

Re: Set analysis/wildmatch/SQL like statement

markobanjanin — Tue, 04 Aug 2020 16:15:10 GMT

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

Re: Set analysis/wildmatch/SQL like statement

Vilius — Wed, 05 Aug 2020 12:23:24 GMT

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

*", "*

*), $(*

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

Re: Set analysis/wildmatch/SQL like statement

jonas_rezende — Wed, 05 Aug 2020 12:40:27 GMT

Hi, @markobanjanin .

Please, share content of your variable.