Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I am trying to pull non-matched records from below table1, & table2.
Expected o/p
Non_Matched
F1, F3
3, C
Table1:
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
Table2:
LOAD * INLINE [
F1, F3
1, A
3, C
6, F
];
Non_Matched:
LOAD
F1,
F3
Resident Table2
Where not Exists(Table1,F1);
But above script not producing any o/p, Can one of you let me Know how to compare table2 records with table1 and then pull only non-matched record of table2.
Thanks,
Ramesh.
Possibly you could use something like this
Table1Temp:
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
Table2:
LOAD * INLINE [
F1, F3
1, A
3, C
6, F
];
Outer Join(Table1Temp)
LOAD
F1,
F3
Resident Table2;
Qualify *;
Non_Matched:
LOAD
F1,
F3
Resident Table1Temp
Where IsNull(F2);
UNQUALIFY *;
Table1:
LOAD
F1,
F2
Resident Table1Temp
Where Not IsNull(F2);
Drop Table Table1Temp;
can you pls try this:
Table1:
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
//Table2:
Right join LOAD * INLINE [
F1, F3
1, A
3, C
6, F
] where not Exists(F1);
Do you want your main tables to be present and the result table be another table?
Hi Sudheep,
Thanks for your response.
Yes i am looking Non_matched records on sperate table and also i wanted to keep orginal input tables as it is.
Thanks,
Ramesh.
Possibly you could use something like this
Table1Temp:
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
Table2:
LOAD * INLINE [
F1, F3
1, A
3, C
6, F
];
Outer Join(Table1Temp)
LOAD
F1,
F3
Resident Table2;
Qualify *;
Non_Matched:
LOAD
F1,
F3
Resident Table1Temp
Where IsNull(F2);
UNQUALIFY *;
Table1:
LOAD
F1,
F2
Resident Table1Temp
Where Not IsNull(F2);
Drop Table Table1Temp;
Hi Ramesh,
Please try the below script and let me know if you have any concerns/questions.
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
Table2:
LOAD * INLINE [
F1 , F3
1, A
3, C
6, F
];
Table3:
LOAD
F1 ,
F2 ,
F1 as F4
Resident Table1;
join
LOAD
F1 ,
F3
Resident Table2;
Non_Matched:
LOAD
F1 as F4,
F3 as F5
Resident Table3
Where not Exists(F4)
;
Drop tables Table3;
RENAME Field F4 to F1;
RENAME Field F5 to F3;
Thanks,
Sibin Jacob.C
Thanks its worked.
Thanks this one also worked.
Hi,
Actually you dont need to do an Outer Join for the result.
Table1:
LOAD * INLINE [
F1, F2
1, A
5, E
6, F
];
Table2:
LOAD * INLINE [
F11, F3
1, A
3, C
6, F
];
Non_Matched:
NOCONCATENATE
LOAD
F1,
F3
Resident Table2
Where not Exists(F11,F1);
DROP TABLE Table2;
This will also work.
Hi Vivek, Thanks for your suggestion.
Can you attach your app, I tried above code and it did not work for me where it is not producing any records in Non_matching table.
Hi,
Please find the attached file.