I have 3 data source QVD's which i need to concatenate into a Qlikview Document displaying only the latest invoice no based on the latest date.
The 3 Qvds will have the same headers (converted in the data load to give this outcome)
I am struggling to see how i can reach this outcome, of only the latest invoice no data based on latest date with 3 qvd's.
I believe i should be using incremental load
Here is the structure of the Sales Table QVD:
LOAD [rowno()], Location, [Retro in Base Currency], [Invoice no.], [Voucher Date], BudgetSalesKey, [Brand Name], [Retailer Name], [Invoicing Customer No.], [Invoicing Customer name], [Customer Invoicing Country], [Item type], [Item No.], [Item Name], [Invoice Item Line Quantity], [Invoice Currency], [Invoice Item Unit Price], [Invoice Item Amount], [VAT Amount], [Invoice Item Total in Invoice Currency], [Base Currency], [Invoice Item Amount in Base Currency], [VAT Amount in Base Currency], [Invoice Item Total in Base Currency], GBP_Price, HKD_Price, DKK_Price, [Invoice Payment Terms], [Account Code], Company FROM [*************\Sales Data.qvd] (qvd);
The key fields are Date [Voucher date] which is set up as calendar and [Invoice no.] which must only have one value.
The previous records must be deleted when new invoice date is found.
There are three QVD's because i am using historic data and a 2 loads of the new data, one where i am converting issue date to voucher date for the process of concatenation in the final qlik view doc. the final has voucher data as Voucher Date.
The same daily files will be added to the 2 loads of the new data qlik view docs, which will then be converted to the same header structure and the tables saved as QVD's
The 3 QVD's must be concatenated and as mentioned, only one Invoice no displayed based on the latest voucher date
I hope this makes sense and somebody is able to help me script the required to achieve no duplication of the invoice no and deletion of the un required data on load.