Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jblomqvist
Contributor II

Can anybody explain how this Where Not Exists function eliminates old value with new value in my data?

Hi all

I have the following data in two tables:

Transactions:

Transactions.JPG

NewTransactions:

NewTransactions.JPG

Transaction ID S0451 in the NewTransactions table above has a new value. I would like to use this new value instead of the value in the Transactions table.

This is my script:

//**Load incremental data**

Transactions:

LOAD Region,

     TransactionID,

     Product,

     Sales,

     ModifiedDate

FROM

[Transcations Insert.xlsx]

(ooxml, embedded labels, table is NewTransactions)

Where ModifiedDate > $(vLastModifiedDate);

//**Original transactions data**

Concatenate(Transactions)

LOAD Region,

     TransactionID,

     Product,

     Sales,

     ModifiedDate

FROM

Transactions.qvd

(qvd)

Where not Exists(TransactionID);

I get the following output:

Capture.JPG

What I don't understand is how Where not Exists(TransactionID) expression works.

Why does it take the new value and deletes the old one? Is it because I have it in the second table and not the first?

3 Replies

Re: Can anybody explain how this Where Not Exists function eliminates old value with new value in my data?

Dear John,

Please do not look it to the "Dialog" window to validate your data. It some times doesnt show the record, even if it exists.

I would recommend you to create a table box on sheet and validate your data.

Regards,

Kaushik Solanki

jblomqvist
Contributor II

Re: Can anybody explain how this Where Not Exists function eliminates old value with new value in my data?

Thanks for the tip Kaushik,

Here is what it looks like in Table Box

Capture.JPG

MVP
MVP

Re: Can anybody explain how this Where Not Exists function eliminates old value with new value in my data?

Your script loads the records from the Excel file that match the date criterion. It then loads records from the qvd where the TransactionID was not already loaded from the Excel file.


So for 1325, it will load the amount from the spreadsheet and it will not load the amount from the QVD as it was already loaded. If you then save the QVD, the amount in the QVD will be updated. It looks correct to me, I have had a similar scenario before; although I don't know the business rules in your case.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser