I am not able to extract the final table as QVD file .
My code is as below, Could someone review and help .
Thank you in advance
EST:
LOAD
[Store] as [Store #],
date(left([In Store Dt & Time],8)) as [Est.Date],
Sum(([DC Cartons Shipped]) )as [Est. Cartons],
date(left([In Store Dt & Time],8))&''&[Store] as key
FROM [lib://PCS SHARED/ACTIVITY.xlsx]
(ooxml, embedded labels, table is Sheet1)where Store<>'~'
Group by [Store],left([In Store Dt & Time],8) ;
Store EST into [lib://PCS SHARED/Activity.QVD](qvd);
//Drop Table EST;
DEL:
LOAD
"Store" as [Store #],
date([Del Date]) as Del.DATE,
SUM(Deliv) as DEL.Cartons,
date([Del Date])&''&[Store] as key
FROM [lib://PCS SHARED/Delivery.xlsx]
(ooxml, embedded labels, table is Sheet1)where Store<>'~' and [Del Date]<>null()
Group by [Store],[Del Date];
Store DEL into [lib://PCS SHARED/Delivery.QVD](qvd);
Drop Table DEL;
Temp1:
LOAD
"Store #",
Est.Date,
"Est. Cartons",
key
FROM [lib://PCS SHARED/ACTIVITY.QVD]
(qvd);
Left Join
LOAD
key,
Del.DATE,
[DEL.Cartons]
FROM [lib://PCS SHARED/Delivery.QVD](qvd);
Temp:
LoAD *,
IF([Est. Cartons]-[DEL.Cartons]>1,"Underage",IF([DEL.Cartons]-[Est. Cartons] >1,"Overage","Match")) as [Discrepancy_+/-1?],
IF([Est. Cartons]-[DEL.Cartons]>5,"Underage",IF([DEL.Cartons]-[Est. Cartons]>5,"Overage","Match")) as [Discrepancy_+/-5?],
[DEL.Cartons]-[Est. Cartons] as [Difference]
Store * from MASTER2 [lib://PCS SHARED/Master.QVD](qvd);
