Find difference between two tables

[kashjaniqlik]

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
];

[#2]

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
];

[boorgura]

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.

[effinty2112 · Accepted solution]

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

[kashjaniqlik]

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