Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Could you perhaps first load the excel data, and after that concatenate load the qvd data using not exists()?
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
Thanks @rwunderlich Got it!