topic Re: join a table on subfields? Column 'ID_SINGLE' > Column > 'ID_MULTI' in Connectivity & Data Prep

join a table on subfields? Column 'ID_SINGLE' > Column > 'ID_MULTI'

ForgotMyOldUsername — Mon, 17 May 2021 13:50:41 GMT

I have two tables , one with a field with a single 'UID', and a second with multiple of of these UID's in a comma delimited string.

In SQL, it'd be fairly trivial to do, you could just have a UDF to split the field and return as a table, but I can't see an obvious way to do it in QLIK.

So join this :

Big_Table
X	Y	UID_MULTI
1	0	A,B,C,D,E
2.221488768	0.496082032	B,C,D,E,F
2.221488768	0.496082032	A,B,C,D,E,F
2.221488768	0.496082032	B,C,D
2.221488768	0.496082032	A,B,C,D
2.221488768	0.496082032	D,E,F
2.221488768	0.496082032	C,D

and

Small_Table
UID	RANK
A	1
B	2
C	3
D	4
E	5
F	6

Re: join a table on subfields? Column 'ID_SINGLE' > Column > 'ID_MULTI'

Lauri — Mon, 17 May 2021 18:41:20 GMT

If you load the table with the SubField function like this, you'll get one row for every value of UID_MULTI:

Big:

Load X, Y, SubField(UID_MULTI, ',') as UID

From ...;

Re: join a table on subfields? Column 'ID_SINGLE' > Column > 'ID_MULTI'

GaryGiles — Mon, 17 May 2021 18:43:38 GMT

You can use the subfield() function:

UID_Link_Table:

Load UID_MULTI,

subfield(UID_MULTI,',') as UID

Resident Big_Table;

Re: join a table on subfields? Column 'ID_SINGLE' > Column > 'ID_MULTI'

ForgotMyOldUsername — Tue, 18 May 2021 07:29:29 GMT

Thanks, @Anonymous & @Lauri ,

An amazingly elegant solution; in my head I'd expected something far more convoluted!

Thanks for taking the time to reply!