11 Replies Latest reply: Nov 25, 2013 4:59 AM by Stephen Matthews RSS

    Peek / Previous Help!

      Dear all please help!

       

      I have been struggling for the past few days with a problem.

      Undoubtedly that the answer must be very simple but I surely cannot see it at all

       

      I have an SQL database with Tickets.

      Between their created date: t_created_date and their closed date: t_closed_date, these tickets go through various stages which are monitored and have their own dates: created_date.

      I am interested in creating an analysis of these intermediary stages basically.

       

      Within a chart / pivot table I was able to do this as following:

       

      =if(Below(created_date)<>Null(),

      dual(

      floor(Interval(Below(created_date)-created_date))

      &' days ' & hour(Interval(Below(created_date)-created_date))

      & ' h ' & minute(Interval(Below(created_date)-created_date))

      & ' min ' & second(Interval(Below(created_date)-created_date))

      & ' sec ', Interval(Below(created_date)-created_date)),

      if(Status<>'closed',

      dual(

      floor(Interval(today()-created_date))

      &' days ' & hour(Interval(today()-created_date))

      & ' h ' & minute(Interval(today()-created_date))

      & ' min ' & second(Interval(today()-created_date))

      & ' sec ', Interval(today()-created_date))

      ,dual(

      floor(Interval(t_closed_date-t_created_date))

      &' days ' & hour(Interval(t_closed_date-t_created_date))

      & ' h ' & minute(Interval(t_closed_date-t_created_date))

      & ' min ' & second(Interval(t_closed_date-t_created_date))

      & ' sec ', Interval(t_closed_date-t_created_date)

      )))

       

      The function will deduct from the previous date the first one for the intermediary stages and if it's closed will deduct from the closed date the open one, if it's not closed it means that the ticket is still open and it will deduct from today's date the last date of the intermediary status.

       

      TicketIDStatus_IDcreated_dateStatusTime In Status
      11154/21/2010 7:34new call0 days 0 h 4 min 4 sec
      18244/21/2010 7:38solution provided397 days 0 h 13 min 0 sec
      112792235/23/2011 7:51closed397 days 0 h 17 min 4 sec
      29154/21/2010 7:39new call1 days 1 h 30 min 46 sec
      2119244/22/2010 9:10analysis19 days 22 h 11 min 10 sec
      2482245/12/2010 7:21re-creation & doc0 days 0 h 4 min 20 sec
      2483245/12/2010 7:25UAT0 days 0 h 11 min 45 sec
      2484235/12/2010 7:37closed20 days 23 h 58 min 1 sec
      321154/21/2010 8:03new call0 days 0 h 2 min 57 sec
      327244/21/2010 8:06req additional info1 days 2 h 34 min 9 sec
      3124244/22/2010 10:40solution provided127 days 2 h 22 min 5 sec
      34208238/27/2010 13:02closed128 days 4 h 59 min 11 sec
      428154/21/2010 8:06new call1 days 1 h 5 min 59 sec
      4120244/22/2010 9:12analysis45 days 23 h 47 min 47 sec
      4107296/7/2010 9:00req additional info365 days 0 h 5 min 30 sec
      41352296/7/2011 9:06solution provided217 days 6 h 3 min 22 sec
      427816231/10/2012 15:09closed629 days 7 h 2 min 38 sec

       

      The problem is that I need the Time in Status to be calculated in script as I need to use it in various other charts.

       

      I've tried defining in script a function: Interval(previous(created_date)-created_date) as Timestamp

       

      but I have the following result (after adding Timestamp to my original chart to see how it looks)

       

      TicketIDStatus_IDCreated_DateStatusTime In StatusTimestamp
      11154/21/2010 7:34new call0 days 0 h 4 min 4 sec-
      18244/21/2010 7:38solution provided397 days 0 h 13 min 0 sec0 days 0 h 4 min 4 sec
      112792235/23/2011 7:51closed397 days 0 h 17 min 4 sec397 days 0 h 13 min 0 sec
      29154/21/2010 7:39new call1 days 1 h 30 min 46 sec
      2119244/22/2010 9:10analysis19 days 22 h 11 min 10 sec1 days 1 h 30 min 46 sec
      2482245/12/2010 7:21re-creation & doc0 days 0 h 4 min 20 sec19 days 22 h 11 min 10 sec
      2483245/12/2010 7:25UAT0 days 0 h 11 min 45 sec0 days 0 h 4 min 20 sec
      2484235/12/2010 7:37closed20 days 23 h 58 min 1 sec0 days 0 h 11 min 45 sec
      321154/21/2010 8:03new call0 days 0 h 2 min 57 sec20 days 23 h 58 min 1 sec
      327244/21/2010 8:06req additional info1 days 2 h 34 min 9 sec0 days 0 h 2 min 57 sec
      3124244/22/2010 10:40solution provided127 days 2 h 22 min 5 sec1 days 2 h 34 min 9 sec
      34208238/27/2010 13:02closed128 days 4 h 59 min 11 sec127 days 2 h 22 min 5 sec
      4120244/22/2010 9:12analysis45 days 23 h 47 min 47 sec1 days 1 h 5 min 59 sec
      4107296/7/2010 9:00req additional info365 days 0 h 5 min 30 sec45 days 23 h 47 min 47 sec
      41352296/7/2011 9:06solution provided217 days 6 h 3 min 22 sec365 days 0 h 5 min 30 sec
      427816231/10/2012 15:09closed629 days 7 h 2 min 38 sec217 days 6 h 3 min 22 sec

       

      I understand that it is normal as previous will return the 1st row as NULL, however everything is lowered with 1 and I am clueless on how to make it right.

       

      Any thoughts?

       

      As you can see also, I didn't calculate the Below(created_date)<>Null() from my chart function in script either ... how could I do this also?

       

      Thank you all,

      Alex

        • Re: Peek / Previous Help!
          Henric Cronström

          If you want to add or subtract where one term is NULL, you cannot use + and -. You should use RangeSum() instead. In other words: Replace

               previous(created_date)-created_date

          wiith

               RangeSum(previous(created_date),-created_date).

           

          Secondly, you cannot make a comparison with NULL. Use IsNull(x) or Len(Trim(x))>0 instead. In other words: Replace

               Below(created_date)<>Null()

          with

               Len(Trim(created_date))>0


          HIC

            • Re: Peek / Previous Help!

              Thank you Henric for your response.

              I will replace with Len(Trim(created_date))>0 , this one is actually perfect, and thank you for this.

               

              As the intermediary states have a date the subtraction is not made with a NULL value.

               

              For Ticket 1 as an example:

              it was a new call at 4/21/2010 7:34 and the status was changed to solution provided at 4/21/2010 7:38

               

              I basically want to know how much time the Ticket stood in the new call status, thus it will be: 4/21/2010 7:38 - 4/21/2010 7:34 = 0 days 0 h 4 min 4 sec and so on for each state.

               

              I replaced the value with rangesum as instructed, however the calculus was wrong, and I go the following results:

               

              TicketIDStatus_IDCreated_DateStatusTime In StatusTimestamp
              11154/21/2010 7:34new call0 days 0 h 4 min 4 sec-40290 days 16 h 25 min 43 sec
              18244/21/2010 7:38solution provided397 days 0 h 13 min 0 sec-1 days 23 h 55 min 56 sec
              112792235/23/2011 7:51closed397 days 0 h 17 min 4 sec
              29154/21/2010 7:39new call1 days 1 h 30 min 46 sec397 days 0 h 12 min 3 sec
              2119244/22/2010 9:10analysis19 days 22 h 11 min 10 sec-2 days 22 h 29 min 14 sec
              2482245/12/2010 7:21re-creation & doc0 days 0 h 4 min 20 sec-20 days 1 h 48 min 50 sec
              2483245/12/2010 7:25UAT0 days 0 h 11 min 45 sec-1 days 23 h 55 min 40 sec
              2484235/12/2010 7:37closed20 days 23 h 58 min 1 sec
              321154/21/2010 8:03new call0 days 0 h 2 min 57 sec20 days 23 h 33 min 47 sec
              327244/21/2010 8:06req additional info1 days 2 h 34 min 9 sec-1 days 23 h 57 min 3 sec
              3124244/22/2010 10:40solution provided127 days 2 h 22 min 5 sec-2 days 21 h 25 min 51 sec
              34208238/27/2010 13:02closed128 days 4 h 59 min 11 sec-128 days 21 h 37 min 55 sec
              428154/21/2010 8:06new call1 days 1 h 5 min 59 sec128 days 4 h 55 min 52 sec
              4120244/22/2010 9:12analysis45 days 23 h 47 min 47 sec-2 days 23 h 7 min 56 sec
              4107296/7/2010 9:00req additional info365 days 0 h 5 min 30 sec-46 days 0 h 12 min 13 sec
              41352296/7/2011 9:06solution provided217 days 6 h 3 min 22 sec-366 days 23 h 54 min 30 sec
              427816231/10/2012 15:09closed629 days 7 h 2 min 38 sec

               

              I tried with peek also Interval(his_created_date-peek('his_created_date',-1), but same as previous, all the results were listed with 1 row below.

               

              Thank you,

              Alex

            • Re: Peek / Previous Help!

              Hi Alex

               

              If I understand you correctly, what you could use is the ALT function.  This allows you to list several alternative calculations, which QV will run through and evaluate, moving onto the next should it not find a relevant results.

               

              So for you example, instead of ;

               

              Interval(previous(created_date)-created_date)

               

              Use;

               

              Alt (  Interval(previous(created_date)-created_date) ,  created_date  )

               

              Thus, should null be returned for you formula, then it will simply populate the field with the created_date (or any other calculation you wish to use).

               

              Hopefully this helps.  Let me know if I've missed the mark.

               


              Regards


              Steve

                • Re: Re: Peek / Previous Help!

                  Hello Steve and thank you for your reply.

                   

                  The alternative functions according to a set of parameters ain't really the problem in this case, as either with alt or with a simple if I can set the ground rules.

                  The problem is that within this set of ground rules, my main function (which is static) is listing the values with 1 row below.


                  Taking my set of data for Ticket 1 and 2 and removing the other clauses now:


                  This is a pivot table and the Time in Status column calculates the time in Status with the formula

                  Interval(Below(created_date)-created_date) - this formula is set as an expression in the pivot table.


                  The Timestamp column is calculating the same thing but with

                  Interval(peek('created_date',-1)-created_date), in the script. I basically need to have the Time calculated in script as I need to use it in other charts as well. {I changed the function to peek from previous .. they did the same in my case anyway}

                   

                  As you can see, all the values in Timestamp are listed with 1 row below, which is giving me a different result than it should.

                  For ex. ticket 2 stood in the re-creation & doc status, 0 days 0 h 4 min 20 sec (which is correct and is calculated correct in the Time in Status Column)

                  However, according to Timestamp it stood there for 19 days 22 h 11 min 10 sec which is wrong as it's the value for analysis from above actually.

                   

                  Untitled.png

                  I know the previous function will make the 1st row NULL (as shown with red in Timestamp) however I need a solution to remove that first row eventually .. or another formula instead of previous/peek .. or something

                   

                  *please ignore the closed value listed with blank in Timestamp. I need to fix this first than move on to the rest.

                   

                  Thank you,

                  Alex

                    • Re: Re: Peek / Previous Help!

                      Alex

                       

                      Sorry if I've grabbed the wrong end of the stick again, but are you saying that you want the TImeStamp to equal the 'Time in Status'. i.e. To be able to replicate this in the script?

                       

                      If this is the case wouldn't the following formula work;

                       

                      Interval(peek('created_date',1)-created_date)

                       

                      Therefore, not using the '-1' but '1'.

                       

                      Can you confirm is firstly if my ascertain is correct?  And let me know if the formula is any good, otherwise you could always perform a join.

                       

                      Regards

                       

                      Steve

                        • Re: Peek / Previous Help!

                          Yes Steve, Time in Status and Timestamp are equal and I am trying to replicate the formula for Time in Status in script under Timestamp.

                           

                          Honestly I tried so many combinations with peek and previous that's not even funny .. plus various other conditions and row counts and etc ..

                           

                          Interval(peek('created_date',1)-created_date) gave this:

                           

                          Untitled.png

                          which is wrong unfortunately.


                          I was thinking of creating a separate table for Timestamp honestly but I was worried about the loading time.

                          To get that initial table I am loading an extensive database which I am filtering.

                          I would basically need to do this twice then and in the second table calculate Timestamp, keep only this column and drop the rest, and join it to the first table with a where exists() clause? Would this be true?

                          Needless to say I do not really know how to do this ..

                           

                          Thank you,

                          Alex

                            • Re: Peek / Previous Help!

                              Hi Alex

                               

                              I have used the join method myself in the past and it worked, however I was new to Qlikview at the time and I thought there must be a more elegant way of going about things.

                               

                              However, if you use this method the steps you require are;

                               

                              1)  Create a key field for each record, e.g. Ticket & '-' & ID & '-' & Status as %Key

                              2) Create a 'PEEK' key.  e.g. Peek(Ticket, -1)  & '-' & Peek(ID, -1)  & '-' & Peek(Status, -1)  as %Key_Prev

                              3) Keep your Interval(peek('created_date',-1)-created_date)  as Timestamp

                              4) Once your table is loaded, use;

                               

                              Left Join (table1) Load

                                   %Key_Prev as %Key,

                                   Timestamp as Peeked_Timestamp

                              Resident (table1);

                               

                              where table1 is your original table load.

                               

                              You can finish this off by ;

                               

                              Drop field Timestamp;

                              Rename field Peeked_Timestamp as Timestamp;

                               

                              This acts to simply move your Timestamp   up one row in effect which is what I believe you are looking for.

                               

                              Let me know how you get on.

                               

                               

                              Steve

                                • Re: Peek / Previous Help!

                                  Hello Stephen,

                                  I am sorry for the late response, it has been a hectic weekend here

                                   

                                  I have been trying all day to apply your solution and I cannot wrap my head around it.

                                   

                                  I have a big SQL database divided into tables.

                                   

                                  1.

                                  One of the first tables is e.`e_i_u`.

                                  In this table I am loading the Tickets' Numbers (Ticket as in)

                                   

                                  Table1:

                                  LOAD `Ticket`;

                                  SQL SELECT *

                                  FROM e.`e_i_u`;

                                   

                                  2.

                                  In another table I have the history of these tickets to say so (in e.`e_i_h`)

                                   

                                  Here I have various columns as following:

                                   

                                  Table9:

                                  LOAD `ID`,

                                      `Iss_ID` as `Ticket`,

                                      `Summary_ID`,

                                      `Status_ID`,

                                      `Created_Date`,

                                   

                                    ID & '-' & Iss_ID & '-' & Summary_ID & '-' & Status_ID as %Key,

                                    peek('ID',-1) & '-' & peek('Iss_ID',-1) & '-' & peek('Summary_ID',-1) & '-' & peek('Status_ID',-1) as %Key_Prev,

                                   

                                    if(Status_ID='23', 'closed',

                                    if(Status_ID='15', 'new call',

                                    if(Status_ID='9',

                                    if(wildmatch(Summary_ID, '*Status changed*'),

                                    purgechar(TextBetween(Summary_ID, 'to ', ' by'), chr(39)),

                                    purgechar(TextBetween(Summary_ID, 'status ', ' by'), chr(39))),

                                    if(Status_ID='24',

                                    if(WildMatch(Summary_ID, '*; Status: *'),

                                    if(wildmatch(textbetween(Summary_ID, '; ', 'by'), '*;*'),

                                    textbetween(textbetween(Summary_ID, '; ', 'by'), '-> ', ';'),

                                    textbetween(textbetween(Summary_ID, '; ', 'by'), '-> ', ')')),

                                    if(wildmatch(Summary_ID, '*(Status:*' & '*;*'),

                                    TextBetween(Summary_ID, '-> ', ';'),

                                    TextBetween(Summary_ID, '-> ', ')'))

                                    ))))) as Status,

                                   

                                  if(len(trim(peek('Created_Date',-1)))>0,

                                  dual(

                                  floor(Interval(Created_Date-peek('Created_Date',-1)))

                                  &' days ' & hour(Interval(Created_Date-peek('Created_Date',-1)))

                                  & ' h ' & minute(Interval(Created_Date-peek('Created_Date',-1)))

                                  & ' min ' & second(Interval(Created_Date-peek('Created_Date',-1)))

                                  & ' sec ', Interval(Created_Date-peek('Created_Date',-1))),

                                  if(Status_ID<>'23',

                                  dual(

                                  floor(Interval(today()-Created_Date))

                                  &' days ' & hour(Interval(today()-Created_Date))

                                  & ' h ' & minute(Interval(today()-Created_Date))

                                  & ' min ' & second(Interval(today()-Created_Date))

                                  & ' sec ', Interval(today()-Created_Date)))) as Timestamp;

                                   

                                  SQL SELECT ID, Iss_ID, Summary_ID, Status_ID, Created_Date FROM e.`e_i_h`

                                  Where Status_ID='15' or Status_ID='23' or Status_ID='24' or Status_ID='9'

                                  Group by Iss_ID, ID, Status_ID;

                                   

                                  Left Join (Table9)

                                  Table10:

                                  Load

                                  %Key_Prev as %Key,

                                  Timestamp as Peeked_Timestamp

                                  Resident Table9;

                                  DROP FIELD Timestamp;

                                  RENAME Field Peeked_Timestamp to Timestamp;

                                   

                                   

                                   

                                  _______________________________

                                   

                                   

                                  Status is a calculated column based on Summary ID (I am basically extracting a set of words from a very long string).

                                   

                                  I defined the regular values and the peeked ones as you said with %Key and &Key_Prev however, I do not know how to include Status also, and after all, Timestamp returns NULL for some reason.

                                  I tried to determine whether Peeked_Timestamp was holding the data from Timestamp but saw that it was actually NULL.

                                   

                                  Thank you,

                                  Alex

                                    • Re: Peek / Previous Help!

                                      Hi Alex

                                       

                                      Sorry I've been off a couple of days.  If the Status does not exist in the table you are creating the key in, you could join this field to the table with a different name, thus using it for the key creation, your load is getting a little more complex and convoluted by doing this, however, it may be the only way.  Without getting my hands on the model and having a toy, I'm struggling to suggest a more elegant solution.

                                       

                                      Or have you solved this independently?

                                       

                                      Regards

                                      Steve

                                        • Re: Peek / Previous Help!

                                          Hello Steve, welcome back!

                                           

                                          I actually managed to work out the solution you gave me and thank you for this

                                           

                                          Speaking of elegant solutions, I actually have a proposal for you: as I am fairly new to Qlikview and still struggling to make sense out of many things I would require a little bit of help, especially on the optimization part of my project. I cannot qualify it as being an extensive one truth be told .. I presume that for a seasoned developer is piece of cake and what I have done in 2 months he or she would have done it in 2 weeks, maybe less.

                                          With this in mind, I would need someone to check a bit the code/tables and help me optimize it/them (or point me on the right track, as I am aware of the fact that the solutions I chose for various tasks were the "lumber Jack's" ones )


                                          I still have 2 modules to make but I am estimating that by Tuesday they will be done .. hopefully ..

                                          I also have 3 more questions related to them, but they should be done on the task itself.


                                          If you would be interested, please drop me a line at raammaam@gmail.com.

                                          I am estimating that overall, this would not take more than 3 hours.


                                          Thank you,

                                          Alex


                                          P.S. The trouble will be taken into consideration of course..

                                            • Re: Peek / Previous Help!

                                              Did my solution work then?  I hope so, if it was if you can mark it as correct/helpful I'd appreciate it.

                                               

                                              Can you let me know what additional assistance you need?  It may be best to post it as a separate discussion, that way if I cannot assist, somebody else may be able to.