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
