Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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
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
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
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