Get differences between two resident tables

[sgonzalez_adaso]

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

[jaibau1993 · Accepted solution]

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]

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]

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]

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