topic Re: Compare column values from two different tables in QlikView

Compare column values from two different tables

Tue, 23 Feb 2016 10:57:07 GMT

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!

Re: Compare column values from two different tables

sunny_talwar — Tue, 23 Feb 2016 19:46:41 GMT

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

Re: Compare column values from two different tables

Wed, 24 Feb 2016 01:23:38 GMT

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.

Re: Compare column values from two different tables

jagan — Wed, 24 Feb 2016 02:47:53 GMT

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.

Re: Compare column values from two different tables

jagan — Wed, 24 Feb 2016 07:55:20 GMT

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.

Re: Compare column values from two different tables

maxgro — Wed, 24 Feb 2016 08:00:21 GMT

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;