topic Re: Get differences between two resident tables in QlikView

Get differences between two resident tables

sgonzalez_adaso — Sat, 16 Nov 2024 03:40:08 GMT

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

Re: Get differences between two resident tables

jaibau1993 — Mon, 06 May 2019 12:37:14 GMT

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.

Re: Get differences between two resident tables

sgonzalez_adaso — Tue, 07 May 2019 11:45:52 GMT

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

Re: Get differences between two resident tables

jaibau1993 — Tue, 07 May 2019 11:58:50 GMT

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,

Re: Get differences between two resident tables

sgonzalez_adaso — Tue, 07 May 2019 13:02:02 GMT

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