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

[ForgotMyOldUsername]

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

[Lauri · Accepted solution]

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 ...;

[GaryGiles]

You can use the subfield() function:

UID_Link_Table:

Load UID_MULTI,

          subfield(UID_MULTI,',') as UID

Resident Big_Table;

[ForgotMyOldUsername]

Thanks, @Anonymous  & @Lauri ,

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

Thanks for taking the time to reply!