Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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.
Hi Philippe
I can't test it right now but it sounds plausible.
I'll mark it answered the minute I can check it.
Someone deleted Table X from my source
Can't test it anymore this week. But I think it'll work, so ... "Correct answer"