Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

11 Replies
Employee
Employee

Re: Peek / Previous Help!

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Community Browser