Where Exists Not working

[Rage4444]

Hi ,

I am trying to use Where not exists and it does not seem to be working.

My Use case:

I have a Previous day data and a current file data. I am using a Key field to compare Current day data with Previous day data and load it in the Final table. If There is a change in the key compared to the previous day file the data will get loaded in final table else it wont. Below is my Code :

PreviousDayData:
LOAD
RegistrationId__c,
RegistrationDate__c,
RegistrationId__c&RegistrationDate__c as Key

from

PreviousDay.qvd (qvd);

NewRegIDMapping:
Mapping
LOAD RegistrationId__c,
'Old'

Resident PreviousDayData;

CurrentDayData:
NoConcatenate
LOAD 
RegistrationId__c,
RegistrationDate__c,

ApplyMap('NewRegIDMapping',RegistrationId__c,'New') as Flag,
RegistrationId__c&RegistrationDate__c as Key

FROM
Main__Data.qvd (qvd);

STORE CurrentDayData into CurrentDay.qvd (qvd);
DROP Table CurrentDayData;

FinalTable:
NoConcatenate
LOAD

RegistrationId__c,
RegistrationDate__c,

Flag,
Key

From CurrentDay.qvd (qvd)
Where Not Exists(Key);
DROP Table PreviousDayData;

PreviousDayQVD:
NoConcatenate
LOAD

RegistrationId__c,
RegistrationDate__c,
RegistrationId__c&RegistrationDate__c as Key

From Main__Data.qvd (qvd);
STORE PreviousDayQVD into PreviousDay.qvd (qvd);
DROP Table PreviousDayQVD;

Can you please help? The where not exist does not seem to working now.

#script #qlikview #syntax @swuehl 

[henrikalmen]

Try this for debugging. Just above FinalTable: you write: qualify *;

On the line directly after the FinalTable is loaded (before Drop table PreviousDayData) you insert a new line: exit script;

Reload the app and check the the data model to see if the tables contains what you think it should. (The "qualify *", if you don't know it already, makes the field names in the foilowing table(s) prefixed with the table name so that there can be no synthetic keys.)

[NadiaB]

Hi @Rage4444 

It will be hard to tell what might be wrong from code alone. It is suggested to review:

The data type/formatting match between the two tables you want to compare. 

If you have dates, make sure you drop time and compare date only if the date is what you want to compare, you can use floor function. 

You can also load the data of both sources separately on different apps and show the data in a straight table so it can be verified the formatting match between both and everything looks ok. 

Other option could be just create demo data, basically one row on each source and verify the comparison is done correctly.

Hope it helps.