topic Re: Find If a Dimension takes some Value for One given Account in QlikView

Find If a Dimension takes some Value for One given Account

Tue, 15 Jul 2014 08:52:55 GMT

My pivot table has a dimension Account and a dimension Product. For each Account, only a subset of all products is available. For example, in the table below, for the value 'Account1' in the Account dimension, there are three values for the Product dimension, P1, P2, P4. For Account2, there are two products: P1 and P3.

ACCOUNT PRODUCT

Acc1 P1

Acc1 P2

Acc1 P4

Acc2 P1

Acc2 P3

I would like to add a calculated dimension which has value Yes if P2 is present for a given account, and No if P2 is not present. To complete the example above, the calculated dimension Has_P2 would be

ACCOUNT PRODUCT Has_P2

Acc1 P1 Yes

Acc1 P2 Yes

Acc1 P4 Yes

Acc2 P1 No

Acc2 P3 No

How can I achieve this ?

Re: Find If a Dimension takes some Value for One given Account

Gysbert_Wassenaar — Tue, 15 Jul 2014 10:20:20 GMT

See attached qvw.

Re: Find If a Dimension takes some Value for One given Account

Tue, 15 Jul 2014 10:29:56 GMT

Thanks a lot for replying.

I cannot read the attached, would you like to describe it briefly ?

Thanks in advance

Re: Find If a Dimension takes some Value for One given Account

Gysbert_Wassenaar — Tue, 15 Jul 2014 10:31:23 GMT

Use account and product as dimension in a straight table and use as expression:

if(count({<ACCOUNT=p({<PRODUCT={'P2'}>})>}ACCOUNT),'Yes',

if(count({<ACCOUNT=e({<PRODUCT={'P2'}>})>}ACCOUNT),'No'))