Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one question to solve. For example I two table: Name A and Name B have following data.
[Table A]
LOAD * INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Mark, 8/2/1967, China
];
[Table B]:
LOAD * INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Jack, 12/5/1953, Thailand
Jack, 19/11/1959, Thailand
Jack, 8/2/1987, Singapore
]
Therefore, my question is how can I check from Table B if the name, DOB, and FromCountry not exists in Table A.
Eg: One person maybe have the same name with another person but they are from difference country so they are different people. Another case is two person have the same name and come from same country but they have different DOB day, so they also are difference people.
How do I get the result in the script?
The result will look like following:
Result:
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Mark, 8/2/1967, China
Jack, 12/5/1953, Thailand
Jack, 19/11/1959, Thailand
Jack, 8/2/1987, Singapore
Thanks for helping.
Regards,
Ming Jiun
This can also work:
[Table A]:
LOAD *,
Name&'|'&Num(DOB)&'|'&FromCountry as Key
INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Mark, 8/2/1967, China
];
Concatenate ([Table A])
LOAD *
Where not Exists(Key, Name&'|'&Num(DOB)&'|'&FromCountry);
LOAD * INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Jack, 12/5/1953, Thailand
Jack, 19/11/1959, Thailand
Jack, 8/2/1987, Singapore
];
DROP Field Key;
Ideally you would have better data with unique ID's for each person and check on them. If your data-source is really not better you would need to combine all fields like: Field1 & '|' & Field2 & '|' & Field3 as KEY and make the check with the exist-function on them (this here Advanced topics for creating a qlik datamodel contained several good examples of exists).
But in your case should be a load with distinct enough to exclude any duplicates, like:
Load distinct * From Source1;
concatenate
Load distinct * From Source2;
- Marcus
This can also work:
[Table A]:
LOAD *,
Name&'|'&Num(DOB)&'|'&FromCountry as Key
INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Mark, 8/2/1967, China
];
Concatenate ([Table A])
LOAD *
Where not Exists(Key, Name&'|'&Num(DOB)&'|'&FromCountry);
LOAD * INLINE [
Name, DOB, FromCountry
Jack, 12/5/1953, Singapore
Jack, 12/5/1953, Thailand
Jack, 19/11/1959, Thailand
Jack, 8/2/1987, Singapore
];
DROP Field Key;
Apologize Marcus, did not read your comment above the distinct concatenate script. You actually proposed the same idea for which I provided the script for. Had no intention of undermining your post.
No problem, with your practically example to my theoretically suggestions you created really an added value