Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III

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
jaibau1993
Partner - Creator III
Partner - Creator III

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.

View solution in original post

4 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

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.

sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

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

jaibau1993
Partner - Creator III
Partner - Creator III

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,

sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

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