topic NOT IN using? in QlikView

NOT IN using?

Tue, 07 Feb 2012 20:52:54 GMT

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;

Re: NOT IN using?

stephencredmond — Tue, 07 Feb 2012 21:19:31 GMT

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

NOT IN using?

Tue, 07 Feb 2012 21:27:52 GMT

Stephen,

That's exactly what I needed.

Thank you!