Peek / Previous Help!

[#1]

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.

Ticket	ID	Status_ID	created_date	Status	Time In Status
1	1	15	4/21/2010 7:34	new call	0 days 0 h 4 min 4 sec
1	8	24	4/21/2010 7:38	solution provided	397 days 0 h 13 min 0 sec
1	12792	23	5/23/2011 7:51	closed	397 days 0 h 17 min 4 sec
2	9	15	4/21/2010 7:39	new call	1 days 1 h 30 min 46 sec
2	119	24	4/22/2010 9:10	analysis	19 days 22 h 11 min 10 sec
2	482	24	5/12/2010 7:21	re-creation & doc	0 days 0 h 4 min 20 sec
2	483	24	5/12/2010 7:25	UAT	0 days 0 h 11 min 45 sec
2	484	23	5/12/2010 7:37	closed	20 days 23 h 58 min 1 sec
3	21	15	4/21/2010 8:03	new call	0 days 0 h 2 min 57 sec
3	27	24	4/21/2010 8:06	req additional info	1 days 2 h 34 min 9 sec
3	124	24	4/22/2010 10:40	solution provided	127 days 2 h 22 min 5 sec
3	4208	23	8/27/2010 13:02	closed	128 days 4 h 59 min 11 sec
4	28	15	4/21/2010 8:06	new call	1 days 1 h 5 min 59 sec
4	120	24	4/22/2010 9:12	analysis	45 days 23 h 47 min 47 sec
4	1072	9	6/7/2010 9:00	req additional info	365 days 0 h 5 min 30 sec
4	13522	9	6/7/2011 9:06	solution provided	217 days 6 h 3 min 22 sec
4	27816	23	1/10/2012 15:09	closed	629 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)

Ticket	ID	Status_ID	Created_Date	Status	Time In Status	Timestamp
1	1	15	4/21/2010 7:34	new call	0 days 0 h 4 min 4 sec	-
1	8	24	4/21/2010 7:38	solution provided	397 days 0 h 13 min 0 sec	0 days 0 h 4 min 4 sec
1	12792	23	5/23/2011 7:51	closed	397 days 0 h 17 min 4 sec	397 days 0 h 13 min 0 sec
2	9	15	4/21/2010 7:39	new call	1 days 1 h 30 min 46 sec
2	119	24	4/22/2010 9:10	analysis	19 days 22 h 11 min 10 sec	1 days 1 h 30 min 46 sec
2	482	24	5/12/2010 7:21	re-creation & doc	0 days 0 h 4 min 20 sec	19 days 22 h 11 min 10 sec
2	483	24	5/12/2010 7:25	UAT	0 days 0 h 11 min 45 sec	0 days 0 h 4 min 20 sec
2	484	23	5/12/2010 7:37	closed	20 days 23 h 58 min 1 sec	0 days 0 h 11 min 45 sec
3	21	15	4/21/2010 8:03	new call	0 days 0 h 2 min 57 sec	20 days 23 h 58 min 1 sec
3	27	24	4/21/2010 8:06	req additional info	1 days 2 h 34 min 9 sec	0 days 0 h 2 min 57 sec
3	124	24	4/22/2010 10:40	solution provided	127 days 2 h 22 min 5 sec	1 days 2 h 34 min 9 sec
3	4208	23	8/27/2010 13:02	closed	128 days 4 h 59 min 11 sec	127 days 2 h 22 min 5 sec
4	120	24	4/22/2010 9:12	analysis	45 days 23 h 47 min 47 sec	1 days 1 h 5 min 59 sec
4	1072	9	6/7/2010 9:00	req additional info	365 days 0 h 5 min 30 sec	45 days 23 h 47 min 47 sec
4	13522	9	6/7/2011 9:06	solution provided	217 days 6 h 3 min 22 sec	365 days 0 h 5 min 30 sec
4	27816	23	1/10/2012 15:09	closed	629 days 7 h 2 min 38 sec	217 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

[hic]

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

[#3]

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:

Ticket	ID	Status_ID	Created_Date	Status	Time In Status	Timestamp
1	1	15	4/21/2010 7:34	new call	0 days 0 h 4 min 4 sec	-40290 days 16 h 25 min 43 sec
1	8	24	4/21/2010 7:38	solution provided	397 days 0 h 13 min 0 sec	-1 days 23 h 55 min 56 sec
1	12792	23	5/23/2011 7:51	closed	397 days 0 h 17 min 4 sec
2	9	15	4/21/2010 7:39	new call	1 days 1 h 30 min 46 sec	397 days 0 h 12 min 3 sec
2	119	24	4/22/2010 9:10	analysis	19 days 22 h 11 min 10 sec	-2 days 22 h 29 min 14 sec
2	482	24	5/12/2010 7:21	re-creation & doc	0 days 0 h 4 min 20 sec	-20 days 1 h 48 min 50 sec
2	483	24	5/12/2010 7:25	UAT	0 days 0 h 11 min 45 sec	-1 days 23 h 55 min 40 sec
2	484	23	5/12/2010 7:37	closed	20 days 23 h 58 min 1 sec
3	21	15	4/21/2010 8:03	new call	0 days 0 h 2 min 57 sec	20 days 23 h 33 min 47 sec
3	27	24	4/21/2010 8:06	req additional info	1 days 2 h 34 min 9 sec	-1 days 23 h 57 min 3 sec
3	124	24	4/22/2010 10:40	solution provided	127 days 2 h 22 min 5 sec	-2 days 21 h 25 min 51 sec
3	4208	23	8/27/2010 13:02	closed	128 days 4 h 59 min 11 sec	-128 days 21 h 37 min 55 sec
4	28	15	4/21/2010 8:06	new call	1 days 1 h 5 min 59 sec	128 days 4 h 55 min 52 sec
4	120	24	4/22/2010 9:12	analysis	45 days 23 h 47 min 47 sec	-2 days 23 h 7 min 56 sec
4	1072	9	6/7/2010 9:00	req additional info	365 days 0 h 5 min 30 sec	-46 days 0 h 12 min 13 sec
4	13522	9	6/7/2011 9:06	solution provided	217 days 6 h 3 min 22 sec	-366 days 23 h 54 min 30 sec
4	27816	23	1/10/2012 15:09	closed	629 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

[#4]

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

[#5]

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.

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

[#6]

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

[#7]

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:

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

[#8 · Accepted solution]

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

[#9]

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

[#10]

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