topic Re: UnderStanding Where Exists() in QlikView

UnderStanding Where Exists()

Anonymous — Tue, 12 Sep 2017 12:05:37 GMT

load * inline [

Region,Sales

A,10

B,20

C,30

D,40

];

Now here I want to load only Region 'C' data using only Where Exists function.Please help me in doing that. I am also trying like this.

load *,1 as flag

Resident A

Where Exists(Region,'C');

Thanks

Re: UnderStanding Where Exists()

sunny_talwar — Tue, 12 Sep 2017 12:07:41 GMT

It would be something like this

LOAD * INLINE [

Region

];

LOAD * INLINE [

Region,Sales

A,10

B,20

C,30

D,40

]

Where Exists(Region);

Re: UnderStanding Where Exists()

its_anandrjs — Tue, 12 Sep 2017 12:08:49 GMT

Try to introduce single inline table

LOAD * Inline

[

RegionToload

];

load * inline [

Region,Sales

A,10

B,20

C,30

D,40

] Where Exists(RegionToload,Region);

Re: UnderStanding Where Exists()

Anonymous — Tue, 12 Sep 2017 12:10:49 GMT

Thanks

Re: UnderStanding Where Exists()

its_anandrjs — Tue, 12 Sep 2017 12:12:05 GMT

Find the attached for the sample example for the explain of the Where Exists

//In this inline table we add single field RegionToload which has only field with Region C to load

ToloadTable:

LOAD * Inline

[

RegionToload

];

//In this we are loading only Region C data by this line Where Exists(RegionToload,Region);

Table1:

load * inline [

Region,Sales

A,10

B,20

C,30

D,40

] Where Exists(RegionToload,Region);

Regards

Anand

Re: UnderStanding Where Exists()

Peter_Cammaert — Tue, 12 Sep 2017 12:13:44 GMT

Where Exists() will filter an ongoing load by analysing a specific column in the current row and checking whether the value in that column exists in a named field ( ! )

The single parameter version of Exists() will check whether the value in column FieldName is present in the field with the same name. That field may not exist before starting the current load, for example if you you want to avoid loading duplicates.

The two-parameter version will check the field specified in the first parameter for existence of the value specified in the second parameter. Usually this second parameter consists of a differently named cell in the current row, or of a concatenation of multiple cells in the current row.

Your code won't do anything useful because you are checking for a static value in a field that has been created before and does contain value 'C' for sure. Which lmeans that all rows will be loaded anyway.

Re: UnderStanding Where Exists()

Anonymous — Tue, 12 Sep 2017 12:13:46 GMT

Thanks Anand

Re: UnderStanding Where Exists()

its_anandrjs — Tue, 12 Sep 2017 12:16:52 GMT

Kaushal,

There is a simple way also as i seen you are trying by adding some table you can do another way also see the inline load by using Where with Like key wod

Where Region like 'C'

load * inline [

Region,Sales

A,10

B,20

C,30

D,40

] Where Region like 'C' ;

Regards

Anand