Get max date in load script

[jmialoundama]

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.

[timpoismans]

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

[Anil_Babu_Samineni]

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;

[jmialoundama]

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 :

[jmialoundama]

Hello ,

I try your code but its get back me 0 rows

[Anil_Babu_Samineni]

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

[jmialoundama · Accepted solution]

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

[Anil_Babu_Samineni]

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