topic Re: where exists not filtering data in QlikView

where exists not filtering data

Wed, 12 Feb 2014 06:56:28 GMT

I am loading "Dealer Id" from a table then filtering the data while loading another table using where exists clause(dealer id,dealer no).

Filtering is done for above table.

then,

concatenate another table

while loading this table using where exists clause(dealer id,dealer no).

but data is not filtered for 2nd table.

why exists not working in 2nd table?

Please help.

Re: where exists not filtering data

Wed, 12 Feb 2014 07:08:55 GMT

i think try according to this,

Table1:

load * from Table1;

join

Table2:

load * from Table2 where exits(dealer id,dealer no) ;

Table11:

load * from Table11;

join

Table22:

load * from Table22 where exits(dealer id,dealer no) ;

Output1:

load * resident Table1;

concatenate

Output2:

load * resident Table11;

drop tables Table1,Table11; // if you change name of field in Output1 and Output2 tables.

Re: where exists not filtering data

er_mohit — Wed, 12 Feb 2014 10:01:43 GMT

I think because in first condition your where exist condition will get those records which is loaded above so far.

so, after again use this exist condition for another table on the basis of filtering table the condition will get only those records which are in all three tables and second time used where exists condition doesn't give you the exact result which you want So, you have to load 2nd table two times like below and make join between them

T1:

LOAD RowNo() as Key,* INLINE [

PId, PValue

1, 10

2, 20

3, 30

4, 40

5, 50

6, 60

];

Join

T2:

LOAD RowNo() as Key,* INLINE [

PId, PValue

4, 70

5, 80

6, 90

7, 100

8, 110

9, 120

]Where Exists(PId);

Join

AgainT2:

LOAD RowNo() as Key,* INLINE [

PId, PValue

4, 70

5, 80

6, 90

7, 100

8, 110

9, 120

];

Join

T3:

LOAD

PId ,PValue Where Exists(PId);

LOAD RowNo() as Key, * Inline [

PId,PValue

7, 15

8, 30

9, 45

10, 60

11, 65

];

Re: where exists not filtering data

Wed, 12 Feb 2014 10:04:49 GMT

why to use join?

I am loading multiple files from 2 different folders. in each folder there are multiple files, for that i use "*" in from clause.

While executing below code, Table1 files get filtered using where exists but Table2 files dont.

Load id

from TableA;

Table1:

Load X, Y

from D:\FolderA\*.xls

where exists (id,X);

concatenate(Table1)

Table2

Load X, Y, Z

from D:\FolderB\*.xls

where exists (id,X);

Re: where exists not filtering data

Wed, 12 Feb 2014 13:31:30 GMT

Hi Santhosh, As per your script, you are loading the data from two folder files having the id only. Please check the Folder B files having the id values.

Re: where exists not filtering data

Thu, 13 Feb 2014 07:02:07 GMT

Yes ,It is having Id values

Re: where exists not filtering data

Thu, 13 Feb 2014 13:21:24 GMT

then it should come. Can you please share some sample data