Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
kashjaniqlik
New 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
Honored Contributor

Re: Find difference between two tables

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

4 Replies
Not applicable

Re: Find difference between two tables

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
Valued Contributor

Re: Find difference between two tables

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
Honored Contributor

Re: Find difference between two tables

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
New Contributor III

Re: Find difference between two tables

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

Community Browser