Hello
I have this script :
TMP_STOCK:
load
"Shop - Code",
Max(Date("DAY SHOP",'YYYYMMDD')) as "Last photo stock"?
Sum("Qty stock") as "Qty stock"
from [$(PathQVD)/DWH\F_SHOP_$(vCurrentYear).QVD](qvd)
where "Shop - Code"=999
and Date("DAY SHOP",'YYYYMMDD')< WeekEnd(today(),-1,0)
Group by "Shop - Code";
I want to retrieve the last date to retrieve the last quantity of stock.
However, I get several dates. I do not see where the problem comes from in my script. Because i should recover only 20180323 with 49 199 in quantity
Thank you in advance.
Hello Anil
Finally I changed the code this way to get back on the last Sunday :
TMP_STOCK:
load
"Shop - Code",
"DAY SHOP",
"Qty stock"
from [$(PathQVD)/DWH\F_SHOP_$(vCurrentYear).QVD](qvd)
where "Shop - Code"=999
and Date("DAY SHOP")= Date(WeekStart(today(),-1));
You can't use Max like this when doing the first load.
What your script is doing right now, is just taking the Max Date from each row. Which is just the Date that's on that row.
You can do your load:
TMP_STOCK:
load
"Shop - Code",
Date("DAY SHOP",'YYYYMMDD') as "Last Photo Stock"?
Sum("Qty stock") as "Qty stock"
from [$(PathQVD)/DWH\F_SHOP_$(vCurrentYear).QVD](qvd)
where "Shop - Code"=999
and Date("DAY SHOP",'YYYYMMDD')< WeekEnd(today(),-1,0)
Group By Shop - Code,[Last Photo Stock];
Then do the following load:
Max:
Load
Max([Last Photo Stock]) AS MaxDate
Resident TMP_STOCK;
LET vMaxDate = Peek('MaxDate', 0, 'Max');
STOCK:
NoConcatenate
Load
*
Resident TMP_STOCK
Where [Last Photo Stock] = $(vMaxDate);
Try this?
TMP_STOCK:
load
"Shop - Code",
"DAY SHOP",
"Qty stock"
from [$(PathQVD)/DWH\F_SHOP_$(vCurrentYear).QVD](qvd)
where "Shop - Code"=999
and Date("DAY SHOP",'YYYYMMDD')< WeekEnd(today(),-1,0);
Right Join(TMP_STOCK)
Load Max("DAY SHOP") as "DAY SHOP" Resident TMP_STOCK;
Hello,
I get a good line but I do not recover the right amount of stock.
I get the amount of global stock instead of recovering the amount of stock corresponding to this day it is 20180923 49 199
In blue is the sum total and in yellow is the quantity i want to recover :
Hello ,
I try your code but its get back me 0 rows
From above code, It will return Only one record. How you are seeing all records. And More over for me it is saying in UI. You can use like
Sum({< [Last photo stock] = {$(=Max([Last photo stock]))} >} [Qte stock])
Hello Anil
Finally I changed the code this way to get back on the last Sunday :
TMP_STOCK:
load
"Shop - Code",
"DAY SHOP",
"Qty stock"
from [$(PathQVD)/DWH\F_SHOP_$(vCurrentYear).QVD](qvd)
where "Shop - Code"=999
and Date("DAY SHOP")= Date(WeekStart(today(),-1));
Thanks for sharing, But your question is not related to load last sunday. BTW, It worked for you.