Where not Exists

[Anonymous]

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

[marcus_sommer]

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

[sunny_talwar · Accepted solution]

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;

[sunny_talwar]

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.

[marcus_sommer]

No problem, with your practically example to my theoretically suggestions you created really an added value