47 Replies Latest reply: Aug 16, 2011 5:43 PM by Stefan Wühl RSS

    Help with expression for showing latest details

    Chris Hopkins

      Hi, i am trying to display the current stock level for every product (for the selected department) for every store.

      I have had some help with this before but still can't get it right.

      My chart currently shows multiple lines for most of the products (this is because every store sends in a file every night detailing their stock details, so QLIKVIEW is showing me every occurence of the product). I only want to see the last file date that was processed (the field is StoreDate).

       

      I want to see 1 line for each product showing the last file processed with the latest LastSoldDate but it has to display the accurate StockLevel for each product.

       

      I have attached a sample.

      Can anyone help please?

        • Help with expression for showing latest details
          Chris Hopkins

          Morning everyone, anyone have any ideas on this please?

            • Re: Help with expression for showing latest details
              Sunil Chauhan

              Please see the attached qvw .

              this might help u

               

               

               

               

               

               

               

              Thanks

              Sunil Chauhan

                • Help with expression for showing latest details
                  Chris Hopkins

                  Hi Sunil, Thanks for your reply.

                  The sample you have attached still show multiple lines per product because there are different LastSoldDates.

                  I need it to only display the line with the LastSoldDate as well as the latest StoreDate..

                   

                  Any ideas?

                    • Re: Help with expression for showing latest details
                      Sunil Chauhan

                      Please see te attached

                       

                       

                       

                       

                       

                       

                      Thanks

                      Sunil Chauhan

                        • Re: Help with expression for showing latest details
                          Chris Hopkins

                          That displays the dates correctly but the stock level isn't being displayed for most of the products...

                            • Help with expression for showing latest details
                              Sunil Chauhan

                              in stock level  expression write Maxstring(Stock level)

                               

                               

                               

                               

                               

                               

                              Thanks

                              Sunil Chauhan

                                • Re: Help with expression for showing latest details
                                  Chris Hopkins

                                  Hi, thnks, but that still doesn't work as Maxstring will give me the higest stock level. I don't want the highest, i want the last one received and processed.

                                   

                                  I have attached a sample again...

                                   

                                  The bottom chart shows every product for every date.

                                   

                                  If you look at the top chart (with your expressions) you will see that RIZLA CIGARETTE PAPER RED  has a stock level of 101

                                   

                                  But if you look at the bottom chart, the ACTUAL stock level for that product is 32 (the one with storedate 26/07/11 and lastsolddate of 26/07/11

                                    • Help with expression for showing latest details
                                      Sunil Chauhan

                                      Take minstrring instead of maxstring

                                        • Re: Help with expression for showing latest details
                                          Chris Hopkins

                                          but wouldnt that give me the lowest stock level??

                                          I need the ACCURATE stock level.

                                            • Help with expression for showing latest details
                                              Sunil Chauhan

                                              at what basis  u want to see this field Please make a clear requirement.

                                              Thanks

                                              Sunil

                                                • Help with expression for showing latest details
                                                  Chris Hopkins

                                                  The ACCURATE stock level would be the one with the latest StoreDate and latest LastSoldDate.

                                                   

                                                   

                                                  Sample.JPG

                                                   

                                                  In the above example, the correct line is the 2nd one down. This is the only line that i want it to display...

                                                  (It has the latest StoreDate with the Latest LastSoldDate)

                                                    • Help with expression for showing latest details
                                                      Sunil Chauhan

                                                      TAKE MAX OF BOTH DATE IN SCRIPT GROUP BY AND RELOAD

                                                       

                                                      I THINK WILL GIVE RESULT

                                                        • Help with expression for showing latest details
                                                          Chris Hopkins

                                                          Sorry Sunil, can you give me an example as i don't know what you mean.

                                                          Thanks

                                                            • Help with expression for showing latest details
                                                              Chris Hopkins

                                                              does anyone have any more suggestions please?

                                                                • Help with expression for showing latest details
                                                                  Sunil Chauhan

                                                                  will u share data  for reload?

                                                                  then only i will say u.

                                                                    • Help with expression for showing latest details
                                                                      Chris Hopkins

                                                                      Hi, Stefan, Thanks that looks very promising!

                                                                      I will put it onto the server and reload it and let you know

                                                                      Thanks!

                                                                        • Help with expression for showing latest details
                                                                          Chris Hopkins

                                                                          Hi Stefan, That expression worked great, thanks very much.

                                                                          I was wondering if you could break down the expression and explain what each part does so i can try and understand it ?

                                                                           

                                                                          if(If(Max(Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)) = LastSoldDate, LastSoldDate) = StoreDate, StockLevel)

                                                                           

                                                                           

                                                                          Thanks

                                                                            • Re: Help with expression for showing latest details
                                                                              Stefan Wühl

                                                                              Hi,

                                                                               

                                                                              here I try:

                                                                               

                                                                              You probably recognize the part which is close to your original expression:

                                                                              Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)

                                                                               

                                                                              --> Return the set of  max. LastSoldDate over Product Code and StoreNumber.

                                                                               

                                                                              The difference to your code is NODISTINCT, because

                                                                               

                                                                              If the expression argument (Max(..)) is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure

                                                                               

                                                                              Your original code missed to return values on multiple appearances of combination of Product Code and Store Number.

                                                                               

                                                                              See the 9th column of attached app to illustrate the issue.

                                                                              With my code (column 10), you will notice that you will get a list of identical values. I used Max to get one (and to be sure, to get the youngest date).

                                                                               

                                                                              (in your original code, you used firstsortedvalue, but this function's help text states:

                                                                              If more than one value of expression share the same lowest sort-order, the function will return null.)

                                                                               

                                                                              Remember that we are still evaluating that expression for every row. So next, compare the found max with the LastSoldDate value of that row. If equal(see column 11), return the date. This just as input for comparison with

                                                                              StoreDate.

                                                                               

                                                                              If this also equals, that means we have found the row with the max LastSoldDate as well as equal StoreDate.

                                                                              Success, return Stocklevel (column 12).

                                                                               

                                                                              Hope this helps,

                                                                              Stefan

                                                                               

                                                                              P.S: Noticed that "you will notice that you will get a list of identical values. I used Max to get one (and to be sure, to get the youngest date)." is not

                                                                              absolutely correct, because you will already get only one value back from the aggregation. So the first Max in my expression isn't really needed.

                                                                               

                                                                              edited by swuehl

                                                                                • Re: Help with expression for showing latest details
                                                                                  Chris Hopkins

                                                                                  Hi, We have just loaded the app onto our sever and i have noticed that i am not getting the results i am expecting..

                                                                                  Having looked at the expression again, i notice that it it giving me the latest [LastSoldDate] then finding the line with a matching [StoreDate].

                                                                                   

                                                                                  if(If(Max(Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)) = LastSoldDate, LastSoldDate) = StoreDate, StockLevel)

                                                                                   

                                                                                  I don't know why i didn't notice it before but that is not what i am looking for...

                                                                                   

                                                                                  I want the expression to find the latest [StoreDate] then find the latest [LastSoldDate]

                                                                                   

                                                                                  Eg.

                                                                                   

                                                                                  The first part of the expression needs to find the latest [StoreDate], so in the below example, the results will be all entries with a [StoreDate] of 03-08-11

                                                                                   

                                                                                  Customer            StoreDate           Product Code    Desc              Stock Level              LastSoldDate

                                                                                  Store A                01-08-11                1234                      Coca Cola            8                             25-07-11

                                                                                  Store A                01-08-11                1234                      Coca Cola            7                             23-07-11

                                                                                  Store A                01-08-11                1234                      Coca Cola            6                             26-07-11

                                                                                  Store A                02-08-11                1234                      Coca Cola            3                             28-07-11

                                                                                  Store A                02-08-11                1234                      Coca Cola            24                           23-07-11

                                                                                  Store A                02-08-11                1234                      Coca Cola            20                           20-07-11

                                                                                  Store A                03-08-11                1234                      Coca Cola            15                           27-07-11

                                                                                  Store A                03-08-11                1234                      Coca Cola            28                           23-07-11

                                                                                  Store A                03-08-11                1234                      Coca Cola            25                           01-08-11

                                                                                  Store A                03-08-11                1234                      Coca Cola            20                           02-08-11

                                                                                   

                                                                                  Next i want the expression to find the latest [LastSoldDate] from the earlier results of [StoreDate] and just display that line.

                                                                                  So in this example above, the only line that i want to be shown is the bottom line. [StoreDate] = 03-08-11 and [LastSoldDate] = 02-08-11.

                                                                                   

                                                                                  any help would appreciated.

                                                                                   

                                                                                  thanks

                                                                                    • Re: Help with expression for showing latest details
                                                                                      Stefan Wühl

                                                                                      Hi,

                                                                                       

                                                                                      I think it could be something like:

                                                                                       

                                                                                      =if(Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate, StockLevel)

                                                                                       

                                                                                      Could you give me an example to validate with your posted app? The Product code 1234 doesn't seem to exist.

                                                                                       

                                                                                      Stefan

                                                                                        • Re: Help with expression for showing latest details
                                                                                          Stefan Wühl

                                                                                          I noticed some duplicate entries per Product and store (using my last posted file) with the above given expression, which I could not explain myself at the moment, but this fixed it:

                                                                                           

                                                                                          =if(Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate

                                                                                          and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel)

                                                                                            • Re: Help with expression for showing latest details
                                                                                              Chris Hopkins

                                                                                              hi Stefan, that worked great, thanks for your help again.

                                                                                              I have noticed another problem though, the data i get is almost perfect, the problem there are products which have never been sold so there is not a [LastSoldDate] on some products which is causing the expression not to show those items.

                                                                                              Is there a way to rectify this? maybe if no [LastsoldDate] is available then just go with the latest [StoreDate]??

                                                                                               

                                                                                              Thanks

                                                                                                • Re: Help with expression for showing latest details
                                                                                                  Stefan Wühl

                                                                                                  Hi,

                                                                                                   

                                                                                                  do these items always show '?' as LastSoldDate?

                                                                                                  Then maybe you could use this:

                                                                                                  =if((Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate or LastSoldDate='?')

                                                                                                  and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel)

                                                                                                    • Re: Help with expression for showing latest details
                                                                                                      Chris Hopkins

                                                                                                      Hi again,

                                                                                                      That worked, although i now get multiple lines on some items. See below...

                                                                                                       

                                                                                                      i am wondering if there is a way to only display the lines with ? as a [LastSoldDate] if no other [LastsoldDate] is available? So in the bottom line for example, as there is a [LastSoldDate] available for that product, the one above would be hidden?

                                                                                                      StockLevels.JPG

                                                                                                      And to answer your qustion, if there is no [LastSoldDate] available, then it will always be '?'.

                                                                                                       

                                                                                                      Thanks

                                                                                                        • Re: Help with expression for showing latest details
                                                                                                          Stefan Wühl

                                                                                                          I tried to accomplish that with using only the lates store date. It seems that there might be multiple entries per store date. Can we distinguish them e.g. by using a timestamp instead of a Date?

                                                                                                            • Re: Help with expression for showing latest details
                                                                                                              Stefan Wühl

                                                                                                              Added a check for single LastStoreDate:

                                                                                                              =if(count(Aggr(NODISTINCT LastSoldDate,[Product Code], StoreNumber))=1,StockLevel,

                                                                                                              if((Aggr(NODISTINCT max(If(Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, LastSoldDate)),[Product Code], StoreNumber) = LastSoldDate or LastSoldDate='?')

                                                                                                              and Aggr(NODISTINCT Max(StoreDate),[Product Code], StoreNumber) = StoreDate, StockLevel))

                                                                                                               

                                                                                                              It's getting hard to read, I think I need to rethink an rework.

                                                                                                               

                                                                                                              One question: Is is possible that an older StoreDate holds a more recent LastSoldDate than a younger StoreDate record? Or will the max(LastStoreDate) always be part of the youngest StoreDate record?

                                                                                                               

                                                                                                              Stefan

                                                                                                                • Re: Help with expression for showing latest details
                                                                                                                  Chris Hopkins

                                                                                                                  Hi again. Unfortunately at the moment we do not have a time stamp field on the file we load in, only a date.

                                                                                                                  There would be multiple entires with the same StoreDate if different stock adjustments were done on the same product the same day.

                                                                                                                  There is a timestamp available on our latest version of software but only half of our customers are on that version, so we cannot load that field in as qlikview will fail when loading in files from the older customers (as it would not be able to find the TimeStamp).

                                                                                                                  If there is no other way of doing it then i will use your earlier expression which shows duplicate entries..

                                                                                                                   

                                                                                                                  Is there a way i can put a dummy [LastSoldDate] (eg. 01/01/70) against the products which ='?'

                                                                                                                    • Help with expression for showing latest details
                                                                                                                      Stefan Wühl

                                                                                                                      Hi,

                                                                                                                       

                                                                                                                      I've posted an expression that should work to filter out duplicates in my previous post, please try.

                                                                                                                       

                                                                                                                      You can replace the '?' with a dummy date on load, if that's an option.

                                                                                                                       

                                                                                                                      My question was a little different:Is it possible, that you have two store dates say, 01/01 (DD/MM) and 02/01, but 01/01 holds the LastSoldDate that is more recent than within 02/01, i.e. the LastSoldDate is being corrected back in my understanding of the data? If this is not possible, I think we could simplify the expression.

                                                                                                                       

                                                                                                                      Regards,

                                                                                                                      Stefan

                                                                                                                        • Help with expression for showing latest details
                                                                                                                          Chris Hopkins

                                                                                                                          Hi Stefan,

                                                                                                                           

                                                                                                                          A newer StoreDate should always have the latest LastSoldDate or the same LastSoldDate as older StoreDates. It will never have an older LastSoldDate.

                                                                                                                           

                                                                                                                          the last expression you gave still shows duplicates as shown below..

                                                                                                                           

                                                                                                                          untitled.JPG

                                                                                                                          How would i go about loading in dummy dates in my script?

                                                                                                                            • Re: Help with expression for showing latest details
                                                                                                                              Stefan Wühl

                                                                                                                              I think it could be like:

                                                                                                                              LOAD

                                                                                                                              ...

                                                                                                                              if(LastSoldDate='?',MakeDate(1900,01,01),LastSoldDate) as LastSoldDate,

                                                                                                                              ...

                                                                                                                              SQL SELECT *

                                                                                                                              from ...

                                                                                                                               

                                                                                                                               

                                                                                                                              (given that LastSoldDate is parsed in as Date Format, otherwise use your current expression from your load  script for the else part of the if clause.)

                                                                                                                               

                                                                                                                              edited by swuehl

                                                                                                                                • Re: Help with expression for showing latest details
                                                                                                                                  Chris Hopkins

                                                                                                                                  The LastStoreDate field is loaded in as a date format automatically.

                                                                                                                                  my old script was:

                                                                                                                                   

                                                                                                                                  StoreProduct:

                                                                                                                                  LOAD

                                                                                                                                   

                                                                                                                                          LastSoldDate,

                                                                                                                                       %LkStoreEAN

                                                                                                                                      

                                                                                                                                  FROM $(cFileName) (qvd);

                                                                                                                                   

                                                                                                                                  and that gave me this LastSoldDate ListBox:

                                                                                                                                   

                                                                                                                                  old.JPG

                                                                                                                                   

                                                                                                                                  I have just tried :

                                                                                                                                   

                                                                                                                                  StoreProduct:

                                                                                                                                  LOAD

                                                                                                                                   

                                                                                                                                       if(LastSoldDate='?',MakeDate(01/01/80),LastSoldDate) as LastSoldDate,

                                                                                                                                      //LastSoldDate,

                                                                                                                                       %LkStoreEAN

                                                                                                                                      

                                                                                                                                  FROM $(cFileName) (qvd);

                                                                                                                                   

                                                                                                                                  and that gave me the following list box:

                                                                                                                                   

                                                                                                                                  old.JPG

                                                                                                                                  As you can see, that removes the ? but no new date of 01/01/80 is shown.

                                                                                                                                  Have i done something wrong?

                                                                                                                                    • Re: Help with expression for showing latest details
                                                                                                                                      Stefan Wühl

                                                                                                                                      I believe MakeDate's argument are Year,Month, Day not Month,Day,Year. Also comma separated and full year.

                                                                                                                                       

                                                                                                                                      So I think it should be

                                                                                                                                      if(LastSoldDate='?',MakeDate(1980,01,01),LastSoldDate) as LastSoldDate,

                                                                                                                                        • Help with expression for showing latest details
                                                                                                                                          Chris Hopkins

                                                                                                                                          Hi Stfan, Sorry to be a pain...

                                                                                                                                           

                                                                                                                                          I have decided to remove the field [LastSoldDate] now as it is causing too many problems. So for the time being i just want the application to display the last date processed.

                                                                                                                                          Maxstring(StoreDate) will give me the latest date, but i am unsurfe how to get the StockLevel related to that date..

                                                                                                                                          Can you help please?

                                                                                                                                           

                                                                                                                                           

                                                                                                                                           

                                                                                                                                          Thanks

                                                                                                                                          Chris

                                                                                                                                            • Help with expression for showing latest details
                                                                                                                                              Stefan Wühl

                                                                                                                                              Hi Chris,

                                                                                                                                               

                                                                                                                                              I think we still may run into trouble, since we have multiple StoreDates with different StockLevel.

                                                                                                                                               

                                                                                                                                              e.g.

                                                                                                                                              StoreNumber 17749

                                                                                                                                              Product Code 548554

                                                                                                                                               

                                                                                                                                              StoreDate 26/07/11

                                                                                                                                               

                                                                                                                                              with Stock Levels 0 and 2

                                                                                                                                               

                                                                                                                                              Which one to pick?

                                                                                                                                               

                                                                                                                                              Do we have another information which one the most recent information is?

                                                                                                                                               

                                                                                                                                              Regards,

                                                                                                                                              Stefan

                                                                                                                                                • Help with expression for showing latest details
                                                                                                                                                  Chris Hopkins

                                                                                                                                                  Ok, i have just been looking into why there are multiple stock levels for some products.

                                                                                                                                                  There will be multiple if there are more than one code for the same product..

                                                                                                                                                  eg

                                                                                                                                                   

                                                                                                                                                     DATE      P-CODE     DESC                    STOCK LEVEL

                                                                                                                                                  26/07/11      548554      CLIPPER REFIL                    0

                                                                                                                                                  26/07/11      547146      CLIPPER REFIL                    2

                                                                                                                                                   

                                                                                                                                                  The above products are exactly the same product but with different PRODUCT CODES (it usually means that one of the products are price marked)

                                                                                                                                                   

                                                                                                                                                  for this a total of the stock level would be needed.

                                                                                                                                                  Sorry, i completely didn't think of linked products...

                                                                                                                                                   

                                                                                                                                                  So what i need then is to show the latest date with a sum of the stoick level for that date...