Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
firstnameunknown
Contributor II
Contributor II

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

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?

Capture.PNG

Labels (1)
16 Replies
chaorenzhu
Creator II
Creator II

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
Master
Master

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

];

firstnameunknown
Contributor II
Contributor II
Author

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

Thank you

firstnameunknown
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Creator II
Creator II

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
Contributor II
Contributor II
Author

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
Master
Master

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

firstnameunknown
Contributor II
Contributor II
Author

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