I am new to Qlikview and thus new to this forum...
I have been struggling with a script in Qlikview for a while now, where a client wants to group occurences if the end time of one occurence is the same as start time of the next (i.e. they occur directly after each other) They should be grouped by the id that occured first, the id's of the adjacent occurences should then be omitted.
The source data (database) looks something like this:
Id Start Time End Time Reason
55 2017-02-24 16:06:58.000 2017-02-24 16:26:58.000 delay
56 2017-02-24 16:26:58.000 2017-02-24 16:56:54.000 switch
57 2017-02-24 16:56:54.000 2017-02-24 17:15:34.000 personnel issue
These three occurences should be grouped by id 55, for example renamed to Group_id a QV table should show:
Group_id Start Time End Time Reason
2017-02-24 16:06:58.000 2017-02-24 16:26:58.000 delay
55 2017-02-24 16:26:58.000 2017-02-24 16:56:54.000 switch
2017-02-24 16:56:54.000 2017-02-24 17:15:34.000 personnel issue
A former co-worker tried to solve it by finding next and previous occurence with the help of 'previous' function on the time columns and ordering the dataset first desc and then asc (creating temporary tables to feed the next table), creating true/false (1/0) columns for Next and Previous and then grouping the occurences if the value in either or both of those columns are 1, but it does not give the desired result.
Below is the first part of the script, where I think the fault lies. (whenlooking at the result of those temporary tables, the next and previous columns are not populated correctly.
if(StartTime=previous(EndTime), 1, 0) as Prev,
0 as Next
ORDER BY Id
if(EndTime=previous(StartTime), 1, 0) as Prev,
0 as Next
ORDER BY Id DESC;