topic Re: Peek / Previous Help! in QlikView

Peek / Previous Help!

Fri, 15 Nov 2013 08:40:30 GMT

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

Re: Peek / Previous Help!

hic — Fri, 15 Nov 2013 08:51:42 GMT

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!

Fri, 15 Nov 2013 09:31:44 GMT

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

Re: Peek / Previous Help!

Fri, 15 Nov 2013 10:12:20 GMT

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!

Fri, 15 Nov 2013 12:45:00 GMT

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

Re: Re: Peek / Previous Help!

Fri, 15 Nov 2013 13:06:59 GMT

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!

Fri, 15 Nov 2013 13:42:40 GMT

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

Re: Peek / Previous Help!

Fri, 15 Nov 2013 14:54:00 GMT

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!

Mon, 18 Nov 2013 10:59:12 GMT

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.

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`;

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!

Wed, 20 Nov 2013 09:27:06 GMT

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!

Sun, 24 Nov 2013 12:16:29 GMT

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!

Mon, 25 Nov 2013 09:59:53 GMT

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.