Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
davyqliks
Creator III
Creator III

Insert update and delete from multiple QVD

Hi,

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.

Thank you in advance, 

Daniel

Labels (4)
1 Reply
siddheshmane
Creator
Creator

Hi,

You can concatenate your 3 qvds and then use FirstSortedValue() to get only the latest record based on the date that you will pass in the function.

FirstSortedValue([InvoiceNumber], - [Voucher Date]) 

negative sign at the voucher date in the function will get you the latest record. 

 

https://community.qlik.com/t5/QlikView-Layout-Visualizations/FirstSortedValue/td-p/317853