Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
kashjaniqlik
Contributor III
Contributor III

Find difference between two tables

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
A001AM700
A002BE680
A003AM691

Cheers

Andrew

View solution in original post

4 Replies
Not applicable

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
Specialist
Specialist

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
Master
Master

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
A001AM700
A002BE680
A003AM691

Cheers

Andrew

kashjaniqlik
Contributor III
Contributor III
Author

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