Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
What exactly are you comparing here? Checking if the key exists in both table or some other comparison?
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.
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.
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.
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;