Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

‌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.

IMG_0822.JPG

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

IMG_0822.JPG

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);

1 Solution

Accepted Solutions
sonkumamon
Creator
Creator

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.

View solution in original post

6 Replies
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
Creator
Creator

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.

Not applicable
Author

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

Not applicable
Author

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

oknotsen
Master III
Master III

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 .

May you live in interesting times!
Hayooga
Contributor
Contributor

In fact ur code works but with Right Join  🙂 

 

cheers