topic Re: Where not exists Client_ID in a specific table in QlikView

Where not exists Client_ID in a specific table

kristof_j — Thu, 20 Mar 2014 14:56:42 GMT

I need to find a Client_ID from table X that didn't exist in table B.

My problem is that all Client_IDs are already loaded in table A.

If I use a where not exists (Client_ID) than it will delete everyone in table X, which I don't want.
Or can I use a where not exists where it will check for the Client_ID in a specific table?

example:

Table A has all Client_IDs
Table B has some Clients
Table X has some Client_ID but I want the one who isn't in Table B
Table Y is my new table with only Client_ID 4

Table A	Table B	Table X	Table Y
Client_ID	Client_ID	Client_ID	Client_ID
1	1	1
2	2
3	3
4		4	4

thanks in advance

Re: Where not exists Client_ID in a specific table

pgrenier — Thu, 20 Mar 2014 15:02:50 GMT

Hello Kristof,

You could accomplish this by adding a new column Client_ID2 to your table which would be a copy of the Client_ID field, but renamed so that you could refer to it later on.

Then, by using the Exists(Client_ID2, Client_ID) condition, you'll be able to apply your desired filtering.

Once you are done with your load, you can remove the extra column by using the Drop Field instruction as such:

DROP Field Client_ID2;

Regards,

Philippe

Re: Where not exists Client_ID in a specific table

gandalfgray — Thu, 20 Mar 2014 15:03:56 GMT

Hi Kristof

I suggest you add a special field when you load table B:

LOAD Client_ID,

Client_ID As IDs_to_exclude

...

And then when you load table X:

Load Client_ID,

...

Where Not Exists(IDs_to_exclude,Client_ID)

//Then you can drop

Drop Field IDs_to_exclude;

Re: Where not exists Client_ID in a specific table

Thu, 20 Mar 2014 15:06:44 GMT

Hi Jacobs, the Where Not Exists should work in the above. Please find the below.

TABLEA:

LOAD Clinet_ID , Clinet_ID AS Client_A INLINE [

Clinet_ID

4 ];

TABLEB:

LOAD Clinet_ID , Clinet_ID AS Client_B

Where Not Exists (Clinet_ID)

LOAD * INLINE [

Clinet_ID

5 ];

In the above script, the Client_ID have: 1 2 3 4 5

for testing, Please make sure that don't keep all the fields are are same.

Re: Where not exists Client_ID in a specific table

kristof_j — Thu, 20 Mar 2014 15:07:31 GMT

Hi Philippe

I can't test it right now but it sounds plausible.

I'll mark it answered the minute I can check it.

Re: Where not exists Client_ID in a specific table

kristof_j — Thu, 20 Mar 2014 16:28:49 GMT

Someone deleted Table X from my source

Can't test it anymore this week. But I think it'll work, so ... "Correct answer"