Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Nolan
Contributor III
Contributor III

Max Day script to prevent duplicate loads on QVD

Good Afternoon,

 

We currently have a scenario where we are loading 2 Sections. The first Section contains a QVD file with historical data up until the previous day, and the Second Section contains the newest day's data concatenated on top of the historical. We then store the historical and new concatenated data as the QVD. 

 

We have been running into a problem where if the script is loaded more than once, the newest day's data is duplicated. Our script currently looks like this:

 

Section 1:

HISTORICAL:

LOAD
"Day",
"Location",
DOH,
"Drop-Ship",
"Ending Inventory",
ID,
"In Transit",
"Lost Sales",
"On Order",
"Product Code",
"Product Desc",
Receipts,
Sales,
"Location" & "Product Code" as WHProdKey
FROM [lib://QVD Storage (ngavin)/INVHISTORYBACKUP.qvd]
(qvd);

Left Join (HISTORICAL)

Load Max("Day") as MaxDay Resident HISTORICAL;

Section 2:

Concatenate
NEW:
LOAD
"Day",
Product as "Product Desc",
"ProductC" as "Product Code",
"Location",
"VALUES - SUM(Ending Inventory C.U.)" as "Ending Inventory",
"VALUES - SUM(Ending On Order C.U.)" as "On Order",
"VALUES - DMADOH(DMA DOH C.U.)" as "DOH",
"VALUES - SUM(In Transit C.U.)" as "In Transit",
"VALUES - SUM(Lost Sales C.U.)" as "Lost Sales",
"VALUES - SUM(Sales C.U.)" as "Sales",
"VALUES - SUM(Receipts C.U.)" as "Receipts",
"VALUES - SUM(852 Drop Ship C.U.)" as "Drop-Ship",
"Location" & "Product" as WHProdKey
FROM [lib:// V2 (ngavin)]
(txt, utf8, embedded labels, delimiter is '\t', msq)

STORE HISTORICAL into [lib://QVD Storage (ngavin)/INVHISTORYBACKUP.qvd]
(qvd);

 

We have been trying to use this "MaxDay" field to write script so the newest Day's data will only be loaded if it exceeds the latest Day present on the QVD. 

For example: 

Where "Day" > "MaxDay", but we receive an error.

 

Does anyone have any suggestions on how to accomplish this? Much appreciated for the help.

 

Nolan

3 Replies
Anil_Babu_Samineni

Create one varaible after you Max table like

LET vMaxDay = Peek('MaxDay', 0, 'HISTORICAL');

Then try this way

Where "Day" > '$(vMaxDay)',

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Nolan
Contributor III
Contributor III
Author

Thank you for the response.

 

Unfortunately, this is still resulting in duplicates of our sales values. Any other suggestions?

Anil_Babu_Samineni

Please read once This 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)