Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

where exists not filtering data

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.

6 Replies
Not applicable
Author

hi

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.

er_mohit
Master II
Master II

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

    ];

//

Not applicable
Author

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);

Not applicable
Author

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.

Not applicable
Author

Yes ,It is having Id values

Not applicable
Author

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