topic Re: Get max date in load script in Francophones

Get max date in load script

jmialoundama — Thu, 27 Sep 2018 11:49:51 GMT

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.

Re: Get max date in load script

timpoismans — Thu, 27 Sep 2018 12:06:03 GMT

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);

Re: Get max date in load script

Anil_Babu_Samineni — Thu, 27 Sep 2018 12:45:01 GMT

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;

Re: Get max date in load script

jmialoundama — Thu, 27 Sep 2018 13:30:19 GMT

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 :

Re: Get max date in load script

jmialoundama — Thu, 27 Sep 2018 13:32:27 GMT

Hello ,

I try your code but its get back me 0 rows

Re: Get max date in load script

Anil_Babu_Samineni — Thu, 04 Oct 2018 13:35:48 GMT

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])

Re: Get max date in load script

jmialoundama — Thu, 04 Oct 2018 13:58:09 GMT

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));

Re: Get max date in load script

Anil_Babu_Samineni — Mon, 08 Oct 2018 13:48:00 GMT

Thanks for sharing, But your question is not related to load last sunday. BTW, It worked for you.