Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
];
//
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);
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.
Yes ,It is having Id values
then it should come. Can you please share some sample data