Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Compare two tables excel

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:

Reference1CountryInformation
123456USAAAAAA
234567UKBBBBB
345678CACCCCC

excel 2:

Reference2CountryCode
456789FRAEEEEE
123456USADDDDD

I think I need to creat a new table:

TABLE:

COUNTRYReferenceInformationCodeFlag
USA123456AAAAADDDDDTRUE
UK234567BBBBBFALSE
CA345678CCCCCFALSE
FRA456789EEEEEFALSE

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

1 Solution

Accepted Solutions

Re: Compare two tables excel

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;

8 Replies

Re: Compare two tables excel

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

marcus_malinow
Valued Contributor III

Re: Compare two tables excel

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;

Not applicable

Re: Compare two tables excel

I need a backend solution

Re: Compare two tables excel

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:


Capture.PNG

Not applicable

Re: Compare two tables excel

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

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;

Not applicable

Re: Compare two tables excel

Thanks it work!

Re: Compare two tables excel

Awesome

Community Browser