Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have 2 tables :
Table 1 : Loan
Table 2 : Excludes
I have to exclude loans from Table 1 which exist in Table 2
Table 1 : Loans
Loan_Number
1
2
3
4
5
6
7
Table 2: excludes :
Loan_Number
1
2
3
Final Output : Table 1: Loans
Loan_Number
4
5
6
7
Can anyone help?
First:
LOAD * Inline [
Loans
1
2
3
4
5
6
7
];
Second:
LEFT JOIN (First)
LOAD *,
1 as Exits;
LOAD * Inline [
Loans
1
2
3
];
Final:
NoConcatenate
LOAD
Loans
RESIDENT First
WHERE IsNull(Exits);
DROP TABLE First;
exclude:
LOAD * INLINE [
exclude
1
2
3
];
loans:
load *
where not Exists(exclude,loans);
LOAD * INLINE [
loans
1
2
3
4
5
6
7
];
Make sure you have not duplicate loan_numbers, otherwise this will not work
First:
LOAD * Inline [
Loans
1
2
3
4
5
6
7
];
Second:
LEFT JOIN (First)
LOAD *,
1 as Exits;
LOAD * Inline [
Loans
1
2
3
];
Final:
NoConcatenate
LOAD
Loans
RESIDENT First
WHERE IsNull(Exits);
DROP TABLE First;
We do have duplicate data in the main(Loans) table.
Can you suggest an alternative?
Try this may be -
Table2:
Load * inline [
Loan_Number_Exist
1
2
3 ];
Table1:
Load * inline [
Loan_Number
1
2
3
4
5
6
7 ]
Where not exists(Loan_Number_Exist,Loan_Number);
Drop Table Table2;