topic Re: Where Exists Not working in App Development

Where Exists Not working

Rage4444 — Mon, 27 Feb 2023 17:41:23 GMT

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

Re: Where Exists Not working

henrikalmen — Mon, 27 Feb 2023 14:16:48 GMT

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?

Re: Where Exists Not working

Rage4444 — Mon, 27 Feb 2023 17:42:46 GMT

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.

Re: Where Exists Not working

henrikalmen — Mon, 27 Feb 2023 19:17:52 GMT

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.

Re: Where Exists Not working

ckarras22 — Tue, 28 Feb 2023 06:56:51 GMT

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.

Re: Where Exists Not working

Gabbar — Tue, 28 Feb 2023 07:02:04 GMT

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);

Re: Where Exists Not working

MayilVahanan — Tue, 28 Feb 2023 07:07:51 GMT

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;

Re: Where Exists Not working

Rage4444 — Tue, 28 Feb 2023 18:33:18 GMT

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

Re: Where Exists Not working

henrikalmen — Tue, 28 Feb 2023 19:58:55 GMT

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.

Re: Where Exists Not working

Rage4444 — Tue, 28 Feb 2023 20:14:24 GMT

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.

Re: Where Exists Not working

henrikalmen — Tue, 28 Feb 2023 20:41:35 GMT

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.)

Re: Where Exists Not working

NadiaB — Fri, 17 Mar 2023 19:30:36 GMT

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.