[9SR6] How to generate date intervals from a list of events

[nstefaniuk]

Hello dear Qliklovers.

I have a question about a typical issue but I haven't found a solution with the search function: I have a table with a list of events, and I want to create the intervals between the events (included or excluded, it's not the question).

In Oracle I can do that with the analytic function LEAD (or LAG) that get the next value for a given key and a given sort. But in Qlikview I haven't found a function that does the trick. I have found a solution with PREVIOUS but very, very ugly.

Here is how I do the trick with Oracle function in the SQL query :

CONNECT TO [...] (...);

event:
Load
id, event_date, event;
SQL
select 1 "id", to_date('01/01/2011', 'DD/MM/YYYY') "event_date", 'IN' "event" from DUAL
UNION
select 1, to_date('01/02/2011', 'DD/MM/YYYY'), 'OUT' from DUAL
UNION
select 1, to_date('01/03/2011', 'DD/MM/YYYY'), 'IN' from DUAL
UNION
select 2, to_date('01/01/2011', 'DD/MM/YYYY'), 'IN' from DUAL
UNION
select 2, to_date('01/04/2011', 'DD/MM/YYYY'), 'OUT' from DUAL
;

interval:
Load
id, event_start, event_end;
SQL
select
"id",
"event_date" "event_start",
lead("event_date", 1) over (partition by "id" order by "event_date") "event_end"
from
(
select 1 "id", to_date('01/01/2011', 'DD/MM/YYYY') "event_date", 'IN' "event" from DUAL
UNION
select 1, to_date('01/02/2011', 'DD/MM/YYYY'), 'OUT' from DUAL
UNION
select 1, to_date('01/03/2011', 'DD/MM/YYYY'), 'IN' from DUAL
UNION
select 2, to_date('01/01/2011', 'DD/MM/YYYY'), 'IN' from DUAL
UNION
select 2, to_date('01/04/2011', 'DD/MM/YYYY'), 'OUT' from DUAL
)
;

The result is:

id event_start event_end
1 01/01/2011 01/02/2011
1 01/02/2011 01/03/2011
1 01/03/2011
2 01/01/2011 01/04/2011
2 01/04/2011

To do the same thing with Qlikview I do (I use the previously loaded table event):

event_sorted:
noconcatenate Load
id As id2,
event_date As event_date2,
event As event2
resident event
order by id, event_date desc;

interval2:
Load
id2 as id2,
event_date2 As event_start2,
If(previous(id2)=id2, previous(event_date2), null()) As event_end2
resident event_sorted;

interval2_sorted:
noconcatenate Load
*
resident interval2
order by id2, event_start2;

drop table interval2;

And the result is good:

id2 event_start2 event_end2
1 01/01/2011 01/02/2011
1 01/02/2011 01/03/2011
1 01/03/2011
2 01/01/2011 01/04/2011
2 01/04/2011

But it's so ugly ! I think that a function must exist to replace the desc sort and this expression "If(previous(id2)=id2, previous(event_date2), null())", but I don't know it.

Thanks a lot for your ideas / remarks / comments

Qlikview version : 9 SR6

[#2 · Accepted solution]

Hello,

I don't see another way than the "previous" / "peek" to make your treatment.

Why did you use 3 Load statement ?

You could not do it in the same LOAD ?

interval:
Load
id,
event_date As event_start,
If(previous(id)= id, previous(event_date), null()) As event_end,
event
resident event
order by id, event_date desc
;

[nstefaniuk]

I have done the 2 first steps to show that the events must be sorted desc, and the third was just to see the data sorted in the model.

So this solution is the only one and can be done in one step.

Thanks