topic Using concat to create a dynamic set modifier in QlikView

Using concat to create a dynamic set modifier

wdonckers — Thu, 20 Apr 2017 09:10:41 GMT

Using the concat function we assemble a set modifier to use in an expression.

Concat( DISTINCT TOTAL <Group> Chr(39) & Key & Chr(39) , chr(44))

when putting this in an expression: this results nicely in following strings depending on the dimension value of the row in the table.

'2','3'

'1','2','3'

'2'

We then want to use this in a set analysis in order to make the set modifier depend on the dimension value of the row in the table.

sum({<Key2={$(=Concat( DISTINCT TOTAL <Group> Chr(39) & Key & Chr(39) , chr(44)))}>}Data)

However it seems the set analysis use '1','2','3' as set modifier for each row, regardless of the dimension value of the row in the table.

Tried to work through a variable (put to concat formula in vKeysInGroup) but that gave the same result.

sum({<Key2={$(=$(vKeysInGroup))}>}Data)

How to solve this? Refer to a simple test app attached.

Re: Using concat to create a dynamic set modifier

Anonymous — Thu, 20 Apr 2017 09:14:31 GMT

try

sum({<Key2=P(Key) >} Data)

if you use set analysis like this, the calculation will be dimensionless.

Could be that you have to use ifs without set analysis.

Re: Using concat to create a dynamic set modifier

wdonckers — Thu, 20 Apr 2017 09:38:14 GMT

Thanks,

Tried that using an if and a substringcount I could solve this as long as I have Key2 as dimension in the table. If not, because i just want to show totals by Group then it does not work.

Re: Using concat to create a dynamic set modifier

swuehl — Thu, 20 Apr 2017 09:48:41 GMT

Set analysis as well as a dollar sign expansion are evaluated once per chart, not per dimensional row.

Have a look at the 'classic' discussion:

Evaluating "sets" in the context of a dimension

Re: Using concat to create a dynamic set modifier

Anonymous — Thu, 20 Apr 2017 09:48:42 GMT

if Key is in the same table as Key2 as it is in your attachment, then

you could use a flag:

LOAD

...

if (Key1=Key2,1) as FLAG;

...

then you select the flag in set analysis:

sum({<FLAG={"1"}>} Data)

------------------------------

if Key and Key 2 are in different tables you could use aggr() or a simple expression like:

sum(if(Key1=Key2,Data)

--> this will sum up row by row even if you don't have Key2 as dimenssion in the table

Re: Using concat to create a dynamic set modifier

wdonckers — Thu, 20 Apr 2017 11:57:50 GMT

In reality I would have about 7000 groups in my application.

That would then mean an if statement with a 7000-fold nested if statement or alternatively the definition of 7000 variables?

That doesn't seem to be a good workaround, does it?

Any other workaround you can think of?

thanks,

Wim

Re: Using concat to create a dynamic set modifier

Anonymous — Thu, 20 Apr 2017 12:00:26 GMT

join everything needed for a flag into one table...