Is possible to delete the intersection data from a left join?

[#1]

‌hello!

I have this situation and I need your help. Im doing a left join with two tables, and this join  create a new table with the first table data and its data intersection like this example with sql statement.

I need to take the data of A with no  intersection data with B like this example:

Im trying to do this using the LOAD statement but it doesnt work.

[My Join]

LOAD 

campo1, //key

campo2, //key

campo3

FROM [tabla A];

LEFT JOIN ([My Join])

LOAD

campo1, //key

campo2, //key

campo4,

campo5

FROM [tabla B];

where IsNull(campo1) and isNull(campo2);

[sunny_talwar]

May be try this

TempTable:

LOAD campo1&'|'&campo2 as KeyField

FROM [tabla B];

FinalTable:

LOAD campo1, //key

    campo2, //key

    campo4,

    campo5

FROM [tabla A];

Where Not Exists(KeyField, campo1&'|'&campo2);

And since you don't want anything from table B, values within campo3 are worthless as there is nothing to attach it with in table A now.

[sonkumamon · Accepted solution]

Try using not exists while loading table B first and then DROP it.

For example:

TableB:

LOAD

    campo1 AS b_campo1 ,

    campo2 AS b_campo2

FROM [tabla B];

TableA:

NoConcatenate

LOAD

    campo1,

    campo2

FROM [tabla A]

WHERE NOT EXISTS(b_campo1, campo1)

AND NOT EXISTS(b_campo2, campo2);

DROP TABLE TableB;

Remember to use NoConcatenate to prevent concatenation of the tables in the loading process.

[#4]

‌hi! thank you very much, it was very helpful for me.

[#5]

‌hi! Thank you Uri Ophir, I use this solution and it work.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[Hayooga]

In fact ur code works but with Right Join  🙂 

cheers