Non Matching records with NOT EXIST

[Anonymous]

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.

[sudeepkm]

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?

[Anonymous]

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.

[CELAMBARASAN · Accepted solution]

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;

[sibin_jacob]

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

[Anonymous]

Thanks its worked.

[Anonymous]

Thanks this one also worked.

[vivek_niti]

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.

[Anonymous]

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.

[vivek_niti]

Hi,

Please find the attached file.