topic Re: Set Analysis in QlikView

Set Analysis

md_qlikview — Sun, 31 May 2015 13:07:50 GMT

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?

Re: Set Analysis

sunny_talwar — Sun, 31 May 2015 13:20:53 GMT

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

Re: Set Analysis

swuehl — Sun, 31 May 2015 13:23:04 GMT

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)

Re: Set Analysis

Anonymous — Sun, 31 May 2015 15:48:46 GMT

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

Re: Set Analysis

sunny_talwar — Sun, 31 May 2015 15:52:11 GMT

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

Re: Set Analysis

Anonymous — Sun, 31 May 2015 16:47:43 GMT

Yes sure i'll do..

Re: Set Analysis

md_qlikview — Sun, 31 May 2015 18:44:46 GMT

thanks for the reply

Thanks much

Re: Set Analysis

md_qlikview — Sun, 31 May 2015 18:47:51 GMT

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

Re: Set Analysis

pratap6699 — Sun, 31 May 2015 20:57:29 GMT

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)

Re: Set Analysis

md_qlikview — Mon, 01 Jun 2015 01:24:17 GMT

thanks pratap for your useful thoughts.

thanks much