Dynamic Population of Table Values

[Anonymous]

Hi All,

I have been struggling with this for quite some time now and now I turn to you all.

I have a table which has IDs and TIER access (as shown below):

ID           TIER-1       TIER-2        TIER-3

AA           YES           NO               NO

BB           YES           NO               YES

CC           NO             NO              YES

Using the following script in the script editor I am trying to pull the Tier names for each ID that has 'YES' under the Tier columns:

DATA_Tier:

LOAD

[ID], Item

WHERE  Bought = 'Yes';

LOAD

  [ID] , 

  Pick(IterNo(),'TIER-1'

  'TIER-2'

  'TIER-3') AS Item,

  Pick(IterNo(),[TIER-1],[TIER-2],[TIER-3]) AS Bought

FROM [lib://Qlik Sense/Test_v1.xlsx]

(ooxml, embedded labels, table is [ALL DATA])

WHILE

IterNo()<=3;

Right now, if I filter out the ID "BB" in the output table, the output that I'm getting is;

ID          TIER

BB          TIER-1

BB          TIER-2

BB          TIER-3

which is incorrect....

My desired output is:

ID          TIER

BB          TIER-1

BB          TIER-3

It should only show those Tier names for which Id: BB has "Yes".

I hope I could explain my problem.

Please help....

[arvind_patil]

Dear kaustabh,

Your table like below:

Backend:

[Sheet1]:

Crosstable (TIER, YESNO, 1)

LOAD

[ID],

[TIER-1] ,

[TIER-2],

[TIER-3]

FROM [lib://AttachedFiles/IDTire.xlsx]

(ooxml, embedded labels, table is Sheet1);

And Your Measure Like Below:

Frontend

Count({<YESNO={'YES'}>}ID)

Thanks,

Arvind Patil

[ogautier62]

Hi

maybe just do this :

new:

load ID,'TIER1' as TIERS resident 'your table' where TIER-1 = 'YES';

load ID,'TIER2' as TIERS resident 'your table' where TIER-2 = 'YES';

load ID,'TIER3' as TIERS resident 'your table' where TIER-3 = 'YES';

regards