topic Re: How to Compare Two Tables with Three Columns to Find Equal Values in QlikView

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

paulosrl — Sat, 04 Feb 2017 14:48:31 GMT

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!

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

swuehl — Sun, 05 Feb 2017 19:43:08 GMT

Maybe like John demonstrated here?

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

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

MarcoWedel — Sun, 05 Feb 2017 22:29:49 GMT

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

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

paulosrl — Mon, 06 Feb 2017 02:11:31 GMT

Thank you Marco, great job, solved my question!

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

MarcoWedel — Mon, 06 Feb 2017 20:36:01 GMT

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

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

paulosrl — Mon, 06 Feb 2017 20:41:11 GMT

Done ! Thanks !