NOT IN using?

[#1]

friends,

How can I use subquery? How?

[Nota01]:

Load * Inline

[

NF, VLR, STATUS,

12345, 12,NF1

12336, 35,NF1

23468, 21,NF1

];

[Nota02]:

Load * Inline

[

NF, VLR, STATUS, GRUPO

12345, 12, NF2, 'TESTE'

12798, 46, NF2, 'ASDAS'

];

[Itens]:

lOAD NF, VLR, STATUS, GRUPO Resident Nota01 WHERE NOT IN  (LOAD NF Resident Nota02);

Drop table Nota01;

[stephencredmond · Accepted solution]

Hi,

QlikView isn't quite like SQL.

The field "NF" doesn't really exist in two separate tables.  Because of the columnar data storage, each column of the same name is just added to by loads.

With a tweak, you could achieve what you want with EXISTS:

[Nota01]:

Load * Inline

[

NF1, VLR, STATUS,

12345, 12,NF1

12336, 35,NF1

23468, 21,NF1

];

[Nota02]:

Load * Inline

[

NF, VLR, STATUS, GRUPO

12345, 12, NF2, 'TESTE'

12798, 46, NF2, 'ASDAS'

];

[Itens]:

noconcatenate

lOAD NF1 as NF, VLR, STATUS Resident Nota01 WHERE NOT Exists(NF, NF1);

Drop table Nota01;

Note the "noconcatenate".  Without it, reloading the same 3 columns again would just auto-concatenate onto Nota01 and you would wonder where all your data has gone after you drop it.

Stephen

[#3]

Stephen,

That's exactly what I needed.

Thank you!