How to Compare Two Tables with Three Columns to Find Equal Values

[paulosrl]

Hi Every one,

I have two tables, each one with three columns (as key) , I woul like to compare two tables to find equal or difrent values

like this

master:

LOAD

     NR_BANCO,

     AGENCIA,

     CONTA

FROM

[MASTER.xlsx]

(ooxml, embedded labels, table is master);

secondary:

LOAD

     NR_BANCO,

     AGENCIA,

     CONTA

FROM

[SECONDARY.xlsx]

(ooxml, embedded labels, table is secondary);

table: master 

NR_BANCO	AGENCIA	CONTA
1	1	2
1	1	3
1	2	1
2	1	1
2	1	2

Table: secondary

NR_BANCO	AGENCIA	CONTA
1	1	2
1	1	3
2	1	1
2	1	2
2	1	10

Result:

NR_BANCO	AGENCIA	CONTA	Result
1	2	1	Exists only in table Master
2	1	10	Exists only in table Secondary

Thanks for help!

[swuehl]

Maybe like John demonstrated here?

How to compare 2 tables and put non-matching results?

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

master:
LOAD *,
    AutoNumberHash128(NR_BANCO, AGENCIA, CONTA) as %Key,
    Ceil(Rand()*100) as SomeMasterFact
FROM [https://community.qlik.com/thread/248717] (html, codepage is 1252, embedded labels, table is @1);

secondary:
LOAD *,
    AutoNumberHash128(NR_BANCO, AGENCIA, CONTA) as %Key,
    Ceil(Rand()*100) as SomeSecondaryFact
FROM [https://community.qlik.com/thread/248717] (html, codepage is 1252, embedded labels, table is @2);

tabResult:
LOAD %Key,
    NR_BANCO,
    AGENCIA,
    CONTA,
    'Master' as SourceTable
Resident master;

LOAD %Key,
    NR_BANCO,
    AGENCIA,
    CONTA,
    'Secondary' as SourceTable
Resident secondary;

DROP Fields NR_BANCO, AGENCIA, CONTA From master, secondary;

Join (tabResult)
LOAD %Key,
    'Exists '&If(Count(DISTINCT SourceTable)=1,'only ')&'in table '&Concat(DISTINCT SourceTable,' and ') as Result
Resident tabResult
Group By %Key;

hope this helps

regards

Marco

[paulosrl]

Thank you  Marco, great job, solved my question!

[MarcoWedel]

You're welcome.

Good to hear that it worked.

If your question is answered then please close your thread by selecting a correct answer:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[paulosrl]

Done ! Thanks !