Set Analysis

[md_qlikview]

Hello Everyone,

I have written an expression which has multiple filters involved in it. Each filter has multiple values to be used in the expression. Since this expression is used in multiple places, i would like to create new fields for each filter in the script and wanted to used those fields in the expression instead of whole long filters with values. So what would be the best possible way to do?

Eg. Country = 'US', 'Japan', 'France', 'Italy', 'Belgium', 'Russia', 'Czeck Republic'

Instead of writing these values i want to create new field which has these values and then want to use it in the expression.

In that case how would be by set expression look like?

[sunny_talwar · Accepted solution]

You can create flags for each of your filter using the if statements. So for the example you listed above, you can create a flag like this:

TableName:

LOAD yourFields,

          Country,

          If(Match(Country, 'US', 'Japan', 'France', 'Italy', 'Belgium', 'Russia', 'Czeck Republic'), 1, 0) as FilterFlag1

FROM.....

and then to use this flag in your expression you can do like this:

=Sum({<FilterFlag1 = {1}>} Sales)

The above expression will only sum sales where the Country matches the ones listed above.

I hope this will be helpful.

Best,

Sunny

[swuehl]

Have you thought about flags?

LOAD

     Country,

     if(Match(Country,'US','Japan','France','Italy','Belgium','Russia','Czeck Republic'),1,0) as MyCountryFlag,

     ...

Then:

=Sum({<MyCountryFlag = {1} >} Sales)

[Anonymous]

Hi

Follow Sunindia and swuehl mentioned Flags using the IF statements. I have tried its working perfectly.

Hope it will fulfill your requirement.

Thank You swuehl‌ & sunindia‌!!

I have learnt new concept today

[sunny_talwar]

No problem qlikview novice‌, I have learned so many new things through this community and I hope you will do the same going forward....

Happy Sharing

Best,

Sunny

[Anonymous]

Yes sure i'll do..

[md_qlikview]

thanks for the reply

Thanks much

[md_qlikview]

i think this is the best possible solution as far as performance and maintenance is considered. i was thinking about creating variable, but this seems to be much promising considering ram utilization and maintenance too.

Thanks sunindia and swuehl

[pratap6699]

yes by using flagfields only we done this....by using match() it's great it look like a IN statement in sql so...

LOAD

if(Match(Country,'US','Japan','France','Italy','Belgium','Russia','Czeck Republic'),1,0) as Flag1;

----;

----

from sourcefile;

expression:sum({<Flag1=>}sales)

[md_qlikview]

thanks pratap for your useful thoughts.

thanks much