topic Re: Where not Exists in QlikView

Where not Exists

Anonymous — Fri, 26 Feb 2016 08:39:36 GMT

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

Re: Where not Exists

marcus_sommer — Fri, 26 Feb 2016 15:13:35 GMT

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

Re: Where not Exists

sunny_talwar — Fri, 26 Feb 2016 15:46:53 GMT

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;

Re: Where not Exists

sunny_talwar — Fri, 26 Feb 2016 16:06:45 GMT

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.

Re: Where not Exists

marcus_sommer — Fri, 26 Feb 2016 16:10:56 GMT

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