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

[jblomqvist]

Hi all

I have the following data in two tables:

Transactions:

NewTransactions:

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:

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?

[kaushiknsolanki]

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]

Thanks for the tip Kaushik,

Here is what it looks like in Table Box

[jonathandienst]

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.