topic Comma Separated Value and Joins in QlikView

Comma Separated Value and Joins

Anonymous — Tue, 05 Apr 2011 15:33:48 GMT

I have a table which has comma separated ID's and another table which is basically a lookup for the ID's. How would I join the two tables?

Table A

PK1 ID1, ID2, ID3

PK2 ID1, ID3

PK3 ID1, ID2

Table B

ID1 Description1

ID2 Description2

ID3 Description3

Thanks,

Dinesh.

pover — Tue, 05 Apr 2011 16:03:42 GMT

Dinesh,

In Table A, create an additional column that will count the number of IDs you have that is

substringcount ( 'ID_List', ',' ) + 1 as No_IDs

Then load a modified Table A in your script to create a one row for each separate ID for each PK through a loop using the "while iterno() " function.

Load PK,
subfield(ID_List,',',iterno()) as ID
Resident TableA
while iterno() <= No_IDs;

That should prepare Table A to be joined with Table B

Regards.

Anonymous — Tue, 05 Apr 2011 21:25:56 GMT

Works like a charm. Thank you.