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]

How is it not working? Are you getting zero rows in your last load?

It seems you missed a part of the load script for the table PreviousDayData in your example above, but I'm thinking that if you don't have a criteria in that part so that everything loads, then all values in the field Key already exists in the data model so there is nothing to load because of "not exists(Key)".

But maybe that's not the case. What does the load for PreviousDayData actually look like? And what result are you getting, and what result were you expecting?

[Rage4444]

Thank you for your reply.

I have also updated my code above.

My Expectation is that I want to compare my current day data with previous day data using this key field and if there is some difference in the date field then load the current day data eg:

Previous File

Reg ID      Date

1000     10-01-22

1001     10-02-23

Current File :

1000     10-01-23

1001     10-02-23

Now if you see that the reg ID is updated with the new registration date and hence key field will change. I want to load the record which is in current file.

What I am getting from this code is that even thought the key field is not changing , the record is still being loaded in the final table. Overall the code should check the current day data and compare it with previous day data and if there is any update in any field in previous day data it should get flagged or then loaded in the final table. 

[henrikalmen]

The exists()-function only compares the field you give it with the (optional) expression you give it. Your statement "Where Not Exists(Key)" makes sure you load data from CurrentDay.qvd but only rows where the value in the field Key doesn't already exists in the data already loaded into qlik.

firstTable:
load * inline [
Key, Date
1000, 10-01-22
1001, 10-02-23
];

secondTable:
load * inline [
Key, Date
1001, 10-01-22
1002, 10-01-22
] where not exists(Key);

In this example, secondTable will contain one row (the row where Key=1002 since that value doesn't already exist in teh Key field. Since all field names are equal in both tables, the row loaded from second table will be automatically inserted into the table firstTable unless you profix the load with "noconcatenate" (but then you would get synthetic keys since two tables have more than one field name in common).

I'm not sure, perhaps you have misunderstood the exists()-function? Or I have misunderstood you.

[ckarras22]

Hello 

Could you try to create a copy of the Key field in PreviousDayData table (Key2) and test against this one in your exist clause (Where Not Exists(Key2))? Then drop this field.

[Gabbar]

your method should work but dont know why it is not working.
Try this :
Current:
load regID,Date, RegID&Date as Key from source;
left join
Previous:
Load regID&Date as Key,'1' as 1 from source;

Final Table:
Load RegId,Date resident current where isnull(1);

[MayilVahanan]

Hi

Try like below

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

RegistrationId__c&RegistrationDate__c as Key2

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(Key2, Key);
DROP Table PreviousDayData;

[Rage4444]

I tried using the Key2 method the script failed for some reason. It is just crashing and asking me to reload the file from the previous loaded data

[henrikalmen]

Your Key field is a combination of the fields RegistrationId__c and RegistrationDate__cm so when doing the "not exists(Key)" filtering, you will make sure that rows will only be loaded into FinalTable if the same combination of RegistrationId__c and RegistrationDate__c in CurrentDay.qvd (i.e. the CurrentDayData table) does not already exist in the PreviousDayData table.

Does that make sense? Is it what you want to do?

And last in your script you load data from a qvd named Main__Data.qvd and store that into a qvd named PreviousDay.qvd - and I suppose that is the same qvd that you load at the top in next reload of the app. I don't really know what Main__Data.qvd is and why it is stored as PreviousDay.qvd but to me it seems that this last part of your script has nothing to do with the comparison you say you want to make.

[Rage4444]

Yes that is what I exactly want to do @henrikalmen .

If there is some change in key field from Current day file and the previous day file does not contain that key then load that record into the final table.

I am not sure why the code does not work. Am I doing something wrong?

The last part of the script just makes the current day data to a previous day data post the comparasion is done. The main_data qvd brings the current day data , its like a incremental type of refresh.

Just to clarify : when the script runs at first the previous day qvd will have yesterday's data and current day will have a live feed data coming from main qvd. 

I hope I was able to explain.