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

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

Mon, 31 Jul 2017 18:35:01 GMT

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

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

sunny_talwar — Mon, 31 Jul 2017 19:47:11 GMT

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.

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

sonkumamon — Mon, 31 Jul 2017 20:01:31 GMT

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.

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

Tue, 01 Aug 2017 00:03:09 GMT

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

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

Tue, 01 Aug 2017 00:07:03 GMT

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

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

oknotsen — Tue, 01 Aug 2017 07:09:34 GMT

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 .

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

Hayooga — Tue, 23 Apr 2019 15:23:45 GMT

In fact ur code works but with Right Join 🙂

cheers