So i have 2 files. from where i'm getting my sales info.
The QVD file are the historical Data. here's all the closed months data. The xslx file are then containing open orders and the last 90 days sales.
So What i'm trying to achieve are to load the qvd file. and then concenate with all the lines, which aren't allready loaded. Since the xlsx file contains 90 days of sales. The 2 files will overlap.
So how to achieve this? My first approach, were to do a peek on maximum invoice date in the qvd file, and then concenate records higher than max date. Problem here are open orders don't have invoice dates. (without going into details, order date can't be used).
Therefor i needed another approach, if i'm to avoid loading order details to another table(which i would prefer).
The data comes without the ID. So i just want to create the ID in the load, on the variable that creates a unique ID. then do a Where not exists ID. which is what i'm trying below.
The problem are that since Unique ID aren't created before the load. Hence i need a preceding load. But this is not working to well for me? I'm thinking maybe i need to load the xlsx to a temp. table. then load the qvd and concenate with the temp. table. and then drop the temp table? Am i correct? Will this be the best approach. if yes. have is this best done? (could some on show the syntax)
The problem are you can't evaluate on the OrderLineID in the xslx load.. I'm guessing it's because the Where not comes before the actual load/creation of each line. therefor qlickview can see all the field from static data, but not the field you are trying to create in the load.