Max Day script to prevent duplicate loads on QVD

Nolan — Mon, 28 Dec 2020 18:56:59 GMT

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

Re: Max Day script to prevent duplicate loads on QVD

Anil_Babu_Samineni — Mon, 28 Dec 2020 19:18:59 GMT

Create one varaible after you Max table like

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

Then try this way

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

Re: Max Day script to prevent duplicate loads on QVD

Nolan — Mon, 28 Dec 2020 19:41:22 GMT

Thank you for the response.

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

Re: Max Day script to prevent duplicate loads on QVD

Anil_Babu_Samineni — Mon, 28 Dec 2020 19:47:07 GMT

Please read once This

topic Re: Max Day script to prevent duplicate loads on QVD in App Development

Max Day script to prevent duplicate loads on QVD

Re: Max Day script to prevent duplicate loads on QVD

Re: Max Day script to prevent duplicate loads on QVD

Re: Max Day script to prevent duplicate loads on QVD