Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmialoundama
Specialist
Specialist

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
jmialoundama
Specialist
Specialist
Author

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
timpoismans
Specialist
Specialist

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
jmialoundama
Specialist
Specialist
Author

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

jmialoundama
Specialist
Specialist
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
jmialoundama
Specialist
Specialist
Author

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)