topic Re: Take new data in App Development

Take new data

Kartik2 — Thu, 07 Mar 2024 12:10:32 GMT

I am taking some data from qvd, the unique key is something like this (*Jan2023) this QVD has data of 2022 2023 and 2024 .
Now i just want the data of 2023 from an qvd file to be replaced by 2023 data taken from the excel file.
all the column names are same in both the files ,also the data like account_id, description is same just the numerical column 'Amount' is different in excel and i want that instead of the 'Amount' column coming from QVD

How can this be done ,using concatenation and where not exists won't work as unique key is same in both the excel and QVD file

Any other way?

Re: Take new data

henrikalmen — Thu, 07 Mar 2024 12:34:44 GMT

Could you perhaps first load the excel data, and after that concatenate load the qvd data using not exists()?

Re: Take new data

rwunderlich — Fri, 08 Mar 2024 19:14:48 GMT

What I think I understand is the the key is *not* unique, which is why you can't use "where not exists(key)". If I'm understanding correctly, I can suggest two approaches.

1) Load the Excel file and then concatenate the QVD file with "where key not like *2023".

2) A more elegant approach as it doesn't rely on hard coding, is to load an extra copy of the key and use that in the not exists.

Load *, key as key2 from excel....;

Concatenate Load * from qvd..
where not exists(key2, key);

-Rob

Re: Take new data

Kartik2 — Fri, 08 Mar 2024 03:57:34 GMT

Thanks @rwunderlich Got it!