topic Re: Compare two tables excel in QlikView

Compare two tables excel

Mon, 26 Oct 2015 15:01:25 GMT

Hello,

I need to compare two tables from different excel. When I get two equal reference I need to write "True" and otherwise "false"

excel 1:

Reference1	Country	Information
123456	USA	AAAAA
234567	UK	BBBBB
345678	CA	CCCCC

excel 2:

Reference2	Country	Code
456789	FRA	EEEEE
123456	USA	DDDDD

I think I need to creat a new table:

TABLE:

COUNTRY	Reference	Information	Code	Flag
USA	123456	AAAAA	DDDDD	TRUE
UK	234567	BBBBB		FALSE
CA	345678	CCCCC		FALSE
FRA	456789		EEEEE	FALSE

Until now, I tried to make an "If" : if(Refence1 <> Reference2, 'true', 'false') as Flag

But I have an issue with this condition, when the charging never stop.

Regards

Re: Compare two tables excel

sunny_talwar — Mon, 26 Oct 2015 15:07:11 GMT

Are you looking to do this in the front end of the application? Will a backend solution also work for you?

Re: Compare two tables excel

marcus_malinow — Mon, 26 Oct 2015 15:10:18 GMT

Try this...

Table:
LOAD Reference1 as Reference,
Country,
Information,
-1 as Source1
FROM
[https://community.qlik.com/thread/189107]
(html, codepage is 1252, embedded labels, table is @1);

Outer Join (Table)
LOAD Reference2 as Reference,
Country,
Code,
-1 as Source2
FROM
[https://community.qlik.com/thread/189107]
(html, codepage is 1252, embedded labels, table is @2);

LEFT JOIN (Table)
LOAD
Reference,
Country,
if(Source1 And Source2, -1, 0) as Flag
RESIDENT Table;

Re: Compare two tables excel

Mon, 26 Oct 2015 15:10:26 GMT

I need a backend solution

Re: Compare two tables excel

sunny_talwar — Mon, 26 Oct 2015 15:12:21 GMT

May be this:

Table1:

LOAD Reference1 as Reference,

Country,

Information

FROM

[https://community.qlik.com/thread/189107]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table1)

LOAD Reference2 as Reference,

Country,

Code

FROM

[https://community.qlik.com/thread/189107]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

If(not IsNull(Information) and not IsNull(Code), 'True', 'False') as Flag

Resident Table1;

DROP Table Table1;

Output:

Re: Compare two tables excel

Mon, 26 Oct 2015 15:24:05 GMT

There is a way to make your condition only with "reference1" and "reference2"? Because I didn't only have 1 column of "information" and "code".

Thanks for your help

Regards

Re: Compare two tables excel

sunny_talwar — Mon, 26 Oct 2015 15:29:10 GMT

May be like this then:

Table1:

LOAD Reference1 as Reference,

Reference1,

Country,

Information

FROM

[https://community.qlik.com/thread/189107]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table1)

LOAD Reference2 as Reference,

Reference2,

Country,

Code

FROM

[https://community.qlik.com/thread/189107]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

If(Reference1 = Reference2, 'True', 'False') as Flag

Resident Table1;

DROP Table Table1;

Re: Compare two tables excel

Mon, 26 Oct 2015 15:37:24 GMT

Thanks it work!

Re: Compare two tables excel

sunny_talwar — Mon, 26 Oct 2015 15:38:34 GMT

Awesome