Where not exists Client_ID in a specific table

[kristof_j]

Hi

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

[pgrenier · Accepted solution]

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

[gandalfgray]

Hi Kristof

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

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;

[#4]

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

1

2

3

4 ];

TABLEB:

LOAD Clinet_ID , Clinet_ID  AS Client_B

Where Not Exists (Clinet_ID)

LOAD  * INLINE [

Clinet_ID

1

2

3

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.

[kristof_j]

Hi Philippe

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

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

[kristof_j]

Someone deleted Table X from my source

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