Compare column values from two different tables

[#1]

Hi All,

May I know the best way that i can compare 2 tables and produce the results as field Flag as Yes/No in the script not in straight table.

Table1:

LOAD * INLINE [

    key, col1, col2

    A, 10, 20

    B, xx, yy

    C, ab, bc

];

Table2:

LOAD * INLINE [

    key, colA, colB

    A, 10, 20

    B, sd, yy

    D, ab, gh

];

Thanks!

[sunny_talwar]

What exactly are you comparing here? Checking if the key exists in both table or some other comparison?

[#3]

Hi,

I want to check based on key field whether value for col1 same with colA.. If yes. it will show flag field as Yes.

[jagan]

Hi,

Try like this

Temp:

LOAD * INLINE [

    key, col1, col2

    A, 10, 20

    B, xx, yy

    C, ab, bc

];

JOIN

LOAD * INLINE [

    key, colA, colB

    A, 10, 20

    B, sd, yy

    D, ab, gh

];

Data:

LOAD

*,

If(col1 = colA, 1, 0) AS col1AndcolAMatchFlag,

If(col2 = colB, 1, 0) AS col2AndcolBMatchFlag,

If(col1 = colA AND col2 = colB, 1, 0) AS MatchFlag

RESIDENT Temp;

DROP TABLE Temp;

Regards,

jagan.

[jagan]

Hi,

Check this

Hi,

Try like this

Temp:

LOAD * INLINE [

    key, col1, col2

    A, 10, 20

    B, xx, yy

    C, ab, bc

];

JOIN

LOAD * INLINE [

    key, colA, colB

    A, 10, 20

    B, sd, yy

    D, ab, gh

];

Data:

LOAD

*,

If(col1 = colA, 'Yes', 'No') AS col1AndcolAMatchFlag

RESIDENT Temp;

DROP TABLE Temp;

Regards,

jagan.

[maxgro · Accepted solution]

With non duplicated key

Table1:

LOAD * INLINE [

    key, col1, col2

    A, 10, 20

    B, xx, yy

    C, ab, bc

];

Table2:

LOAD * INLINE [

    key, colA, colB

    A, 10, 20

    B, sd, yy

    D, ab, gh

];

Check2Tables:

load key, col1 & '|' & col2 as field1 Resident Table1;

join (Check2Tables) load key, colA & '|' & colB as field2 Resident Table2;

Left Join (Check2Tables)

load key, field1=field2 as Flag

Resident Check2Tables;