Qlik Community

Groupe des Utilisateurs Francophones

Vous êtes francophone? Ce groupe est pour vous. Venez découvrir comment démarrer avec Qlik Sense et QlikView, poser vos questions et partager vos tutos et astuces avec les membres de notre communauté.

jmialoundama
Contributor II

Get max date in load script

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.


Capture.JPG

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.

 

1 Solution

Accepted Solutions
Highlighted
jmialoundama
Contributor II

Re: Get max date in load script

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

View solution in original post

7 Replies
Highlighted
timpoismans
Valued Contributor

Re: Get max date in load script

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

Highlighted

Re: Get max date in load script

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
jmialoundama
Contributor II

Re: Get max date in load script

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 :

Capture_LI.jpg

Highlighted
jmialoundama
Contributor II

Re: Get max date in load script

Hello ,

I try your code but its get back me 0 rows

Highlighted

Re: Get max date in load script

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
jmialoundama
Contributor II

Re: Get max date in load script

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

View solution in original post

Highlighted

Re: Get max date in load script

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)