Need to restrict rows

[swati_rastogi27]

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?

[giakoum]

exclude:

LOAD * INLINE [

    exclude

    1

    2

    3

];

loans:

load *

where not Exists(exclude,loans);

LOAD * INLINE [

    loans

    1

    2

    3

    4

    5

    6

    7

];

[giakoum]

Make sure you have not duplicate loan_numbers, otherwise this will not work

[Anonymous · Accepted solution]

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;

[swati_rastogi27]

We do have duplicate data in the main(Loans) table.

Can you suggest an alternative?

[Digvijay_Singh]

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;