5 responses to “Cumulative Values In the Script Using Peek()”

1. Hi Richard,
   This is a very elegant script solution for inter-record comparisons.

   However, I have an example where I would like to make use of something similar but am struggling to make it work and wondered if you had any ideas to point me in the right direction – any help appreciated!!

   I have say the following data:

   PersonID, EventRank, EventName, EventDate
   1000, 1, Allocation Begins, 01/01/2015
   1000, 2, Allocation Ends, 02/01/2015
   1000, 3, Visit, 03/03/2015
   1000, 4, Assessment Begins, 04/04/2015
   1000, 5, Assessment Ends, 05/04/2015
   1000, 1, Allocation Begins, 06/06/2015
   1000, 2, Allocation Ends, 07/06/2015
   1000, 4, Assessment Begins, 08/08/2015
   1000, 5, Assessment Ends, 09/08/2015
   1000, 6, Closure Begins, 10/10/2015
   1000, 7, Closure Ends 11/10/2015

   What my client would like, for each record(row) is to have a Field (PreviousEventDate) which will return the most recent date of the Event with the next rank down that’s before the current event date for that row. So for the row with Visit (rank 3) it looks for rank 2 but retrieves the max date for these occurring before the visit row = 02/01/2015 and not 07/06/2015.
   If the rank=1 it should return a Null.

   Finally, it needs to scale so that it works for each unique PersonID.
   I tried using peeks and previouses and indeed in the front-end, Aboves and RowNos but without any joy 😦

   Hope this makes sense and thanks for your time,

   Elliot

   LikeLike

   • Hi Elliot,

     Peek or previous should get the correct results as long as you sort the data correctly.

     Perhaps first sorted value will help here also. I’ve done a couple of posts around how that be be used. https://qlikcentral.com/2014/08/13/firstsortedvalue/

     You could reload the data into a mapping table with the first column being a Concat field of personid &’|’& event rank. The second column is the date.
     All you need to do then is applymap with the expression personid &’|’& event rank -1.

     Note the -1. It will return the date of the previous event.

     Hope that works for you.

     Take care.

     Richard.

     LikeLike

2. Thanks for the speedy reply, Richard.
   The ApplyMap approach using the Concat of PersonId and EventRank was an approach I tried, however as there are sometimes more than one occurrence of the previous event rank – the mapping only retrieves the first occurrence – if I use Max(EventDate) in the Mapping Load, I will get dates of that event rank which occur after the current event. What I need is the Max value which precedes the current event date – so for the record 1000, 2, Allocation Ends, 07/06/2015 i need to retrieve the date (for rank 1) 06/06/2015 and not 01/01/2015 but for the record, 1000, 2, Allocation Ends, 02/01/2015 I do need the 01/01/2015 as that is the most recent occurrence of rank 1 which pre-dates 02/01/2015.

   Hope this is clear – I know I’m missing something very obvious 🙂

   Thanks
   Elliot

   LikeLike

3. Why not in the mapping table group by eventid (or possibly eventname) and personid and use the max date? Then you should have the latest date and only one instance for each eventid so applymap will work.

   LikeLike

   • I tried using this:

     MAPPING
     LOAD
     PersonID & ‘|’ & EventRank,
     Date(Max(EventDate),’DD/MM/YYYY’)
     Resident SourceData
     Group By PersonID & ‘|’ & EventRank
     ;

     but even though the Max ensures one value per concat id (PersonID & ‘|’ & EventRank) and I’m using
     ApplyMap(‘PrevDateMap’,PersonID & ‘|’ & Num(EventRank-1)
     to retrieve the maximum date for rows with rank one lower, that date may be greater than the date of the current row – I still need to retrieve the maximum date for dates less than the current row ??

     PS: really appreciate the help

     LikeLike