topic Re: How to do IN statement in LOAD Script in QlikView

How to do IN statement in LOAD Script

Fri, 16 Dec 2011 10:11:58 GMT

I have the need to perform a task similar to the SQL IN statement.

I am performing two load processes into temporary tables:

Table1:

LOAD

column1,

column2,

column3

FROM

[..\..\DATA\DATASET1.qvd] (qvd)

WHERE

arguements here;

Table2:

LOAD

column4,

column5,

column6

FROM

[..\..\DATA\DATASET1.qvd] (qvd)

WHERE

different arguements here;

What I would like to do after is something like this:

Table3:

LOAD

Column7,

Column8,

Column9

FROM

[..\..\DATA\DATASET1.qvd] (qvd)

WHERE

Column7 NOT IN RESIDENT (Table2) Column4;

Any help is greatly appreciated.

Graham

Re: How to do IN statement in LOAD Script

luciancotea — Fri, 16 Dec 2011 10:15:29 GMT

1. In your example, there will be only Table1, because Table2 will automatically concatenate to Table1 due to similar columns.

2. If you want to load only values that are not present already in a loaded column, use EXISTS()

Table3:

LOAD

Column1,

Column2,

Column3

FROM

[..\..\DATA\DATASET1.qvd] (qvd)

WHERE

not EXISTS( Column1);

Re: How to do IN statement in LOAD Script

Fri, 16 Dec 2011 10:33:29 GMT

i changed the example to show two distinct datasets, in my case they would not be merged as these temp tables are never consumed in the ui this is part of a process to build a qvd.

that being said, how does, where not exists (column1) reference the different dataset?

I want to load everything into resident table3 where the column7 does not contain any of the loaded values in temp table2 column4.

Re: How to do IN statement in LOAD Script

luciancotea — Fri, 16 Dec 2011 10:44:11 GMT

Table3:

LOAD *

WHERE not EXISTS( Column4, Column7)

;

LOAD *

FROM

[..\..\DATA\DATASET1.qvd] (qvd);

Check out the help for detailed information about EXISTS() function, and do some testing.

Re: How to do IN statement in LOAD Script

Thu, 06 Nov 2014 04:46:37 GMT

Table2:

column4,

column5,

column6,

column4 as column7_reference

from Qvd;

Table3:

Load

column7,

column8,

column9

from Qvd

where not exists(column7_reference,column7)

This helps in checking the existence only in Table2.