topic Re: Find difference between two tables in QlikView

Find difference between two tables

kashjaniqlik — Wed, 13 Jul 2016 14:44:40 GMT

Hello

I have below (sample) two tables with each loading some date. I'm concacting both tables.

While I concatanate, I would like to flag the values that are not matching through a new field. In below example, ExternalID A001 in TableA is not found in other table. Could I use anything in Qlikview that will help me add a flag (say new value i.e. A001)?

I could then use this flag to identify in qlikview which new values have been added.

Many Thanks for your help.

TableA:
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 70, A001
BE, 68, A002
];
Concatenate
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 69, A003
BE, 68, A002
];

Re: Find difference between two tables

Wed, 13 Jul 2016 15:14:16 GMT

I think abaout this:

TableA:
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 70, A001
BE, 68, A002
];

Concatenate

load *, 'not find' as flag where not exist (ExternalID);
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 69, A003
BE, 68, A002
];

Concatenate

load *, where exist (ExternalID);
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 69, A003
BE, 68, A002
];

Re: Find difference between two tables

boorgura — Wed, 13 Jul 2016 15:19:36 GMT

TableA:
Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 70, A001
BE, 68, A002
];
Concatenate

LOAD *

where not exists(ExternalID)

;

Load * INLINE
[
Country, SnapshotNo, ExternalID
AM, 69, A003
BE, 68, A002
];

**Please be aware - the above approach works for distinct ExternalIDs - if they are not distinct, you will have create a temp table, with distinct ExternalIDs as field with a different name - and then use that in that in the where clause.

Re: Find difference between two tables

effinty2112 — Wed, 13 Jul 2016 15:59:29 GMT

Hi Kash,

Yet another suggestion:

TableA:

Load * INLINE

[

Country, SnapshotNo, ExternalID, Flag

AM, 70, A001,0

BE, 68, A002,0

];

NoConcatenate

TableB:

LOAD

Country, SnapshotNo, ExternalID as ID;

Load * INLINE

[

Country, SnapshotNo, ExternalID

AM, 69, A003

BE, 68, A002

];

Concatenate(TableA)

LOAD

Country, SnapshotNo, ID as ExternalID,

If(not Exists(ExternalID,ID),1,0) as Flag

Resident TableB;

Drop table TableB;

A flag value of 1 will indicate a new value of ExternalID.

ExternalID	Country	SnapshotNo	Flag
A001	AM	70	0
A002	BE	68	0
A003	AM	69	1

Cheers

Andrew

Re: Find difference between two tables

kashjaniqlik — Wed, 13 Jul 2016 17:27:35 GMT

Thanks very much for your time and suggestions. Much appreciated.