Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Get differences between two resident tables

Hello,

I need to get the difference between two resident tables in order to do or not to do one action.

My tables are very simple:

[HistoricalData]:

NOCONCATENATE LOAD Distinct "Id" AS "HistoricalId"

RESIDENT [HistoricalTable];

[CurrentData]:

NOCONCATENATE LOAD Distinct "Id" AS "IdToCheck"

RESIDENT [CurrentIdList];

 

I need to know if there are some values on [CurrentData] that doesn't exist on [HistoricalData].

If some Id from historical doens't exist on [CurrentData] it doens't matters. 

My final point could be a variable with 0 if there is no differences or with 1 if there are some Ids on Current.

 

I'd tried with left joins but I'm always have a 0! (and it's not).

 

Thanks in advance.

Sergio

Labels (3)
1 Solution

Accepted Solutions
Partner
Partner

Re: Get differences between two resident tables

Hi!

You may use the function "Exists". Try simething like:

historical:
LOAD * INLINE [
    HistoricalId
    1
    2
    3
    4
    6
];

current:
NoConcatenate LOAD * INLINE [
    IdToCheck
    1
    2
    3
    10
];

InCurrentNotInHist:
NoConcatenate LOAD IdToCheck Resident current Where not Exists(HistoricalId, IdToCheck);

if NoOfRows('InCurrentNotInHist') > 0 then
	LET vCheck = 1;
ELSE
	LET vCheck = 0;
ENDIF

Bests,

Jaime.

4 Replies
Partner
Partner

Re: Get differences between two resident tables

Hi!

You may use the function "Exists". Try simething like:

historical:
LOAD * INLINE [
    HistoricalId
    1
    2
    3
    4
    6
];

current:
NoConcatenate LOAD * INLINE [
    IdToCheck
    1
    2
    3
    10
];

InCurrentNotInHist:
NoConcatenate LOAD IdToCheck Resident current Where not Exists(HistoricalId, IdToCheck);

if NoOfRows('InCurrentNotInHist') > 0 then
	LET vCheck = 1;
ELSE
	LET vCheck = 0;
ENDIF

Bests,

Jaime.

Partner
Partner

Re: Get differences between two resident tables

Hi Jaime,

 

Thx for your reply.

I'm trying to do like this, but i'm always getting a 0 on the variable.

 

It's possible that a join is needen before the where not exist??

 

Thank you,

Sergio

Partner
Partner

Re: Get differences between two resident tables

Hi Sergio.

I don't think we need a join (you can execute the above code and you'll see that it works). Maybe HistoricalID and IDTocheck have differences (blank spaces or different formats) and this is why the join nor the Exists works.

Could you provide a sample of IDs so we can test what is actually happening?

Jaime,

Partner
Partner

Re: Get differences between two resident tables

My fault!

I'm working with different modules and I was using the worng column name.

Now it's working fine. I'm getting the correct value.

 

Thanks for your help!

Sergio