How to join two tables if the primary keys have two values

[firstnameunknown]

Table A: Primary key is contact id but some cells have two contact id in it.

Table B: Contact id is the primay key. Contact name.

I want to left join Table B into Table A, so the contact names would show in the same column with comma seperared according to the contact id in the Table A.

How can I do it?

[chaorenzhu]

Would suggest you to split comma separated values in Table A into separate rows then perform join row by row. You can use subfield()

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

[anat]

load id,name,subfield(cid,',') as cid;
load * inline [
id,name,cid
1,a,"11,12"
2,b,"15"

];

[firstnameunknown]

What if I want to split all the contact ids? and contact id can still be the primary key?

Thank you

[firstnameunknown]

Hi This is working. but after I merge two tables,  contact name and emails are empty for id 1. It only shows in id 2 and 3. Any ideas? Thanks

[firstnameunknown]

Hi This is working. but after I merge two tables,  contact name and emails are empty for id 1. It only shows in id 2 and 3. Any ideas? Thanks

[chaorenzhu]

Not sure why for your case since I'm able to produce the desired output. Can share your load script to take a look?

[firstnameunknown]

Load id,
name,
subfield(contactid,",")
FROM [lib://ClientData/****************.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

left join
LOAD
contactid as PrimaryKey,
name as "Contact First Name",
email as "Contact Email Address"
FROM [lib://ClientData/$$$$$$$44$$44$$$44$$.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

drop field PrimaryKey;
STORE Account_New INTO [lib://ClientData/1111111111111111112.csv] (txt);

exit Script;

[anat]

Can u name :subfield(contactid,",") As PrimaryKey

[firstnameunknown]

It works! But now how do I combine two rows of id 1 togethe to one row, as I listed at the first screenshot ?