Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

terrusie
New Contributor II

Duplicate timestamps

The data I'm trying to analyze is collected with timestamps that are only accurate to the minute, as such there is a significant number of events that have the same start/ending time.  The scenarios that show up the most fall into the following categories:

1.  The wrong task was clocked in, and corrected by immediately clocking out and clocking back in on the right task (NULL indicates that the task is still in progress).

StartFinishTask
05/01/2018 06:01NULLTask 2
05/01/2018 06:0105/01/2018 06:01Task 1

2.  The same as above, except that the task has been completed

StartFinishTask
05/01/2018 06:0105/01/2018 04:01Task 2
05/01/2018 06:0105/01/2018 06:01Task 1

My problem is that when I try to do a FirstOrderedSearch(Task, -Start), it returns a null, and I need to be able to select either the task that has a NULL Finish value (Scenario 1) or the latest Finish value (Scenario 2).

3 Replies

Re: Duplicate timestamps

May be something like this

FirstSortedValue(Task, -(Start + Alt(Finish, 100000)/1E10))

Re: Duplicate timestamps

Tested both here

//Table:

//LOAD Start,

// If(Len(Trim(Finish)) > 0, Finish) as Finish,

// Task;

//LOAD * INLINE [

//    Start, Finish, Task

//    05/01/2018 06:01, , Task 2

//    05/01/2018 06:01, 05/01/2018 06:01, Task 1

//];


Table:

LOAD Start,

If(Len(Trim(Finish)) > 0, Finish) as Finish,

Task;

LOAD * INLINE [

    Start, Finish, Task

    05/01/2018 06:01, 05/01/2018 04:01, Task 2

    05/01/2018 06:01, 05/01/2018 06:01, Task 1

];

terrusie
New Contributor II

Re: Duplicate timestamps

Sunny,

Thank you.  This is perfect, because it has the effect of reloading the data with nulls replaced by +inf, yet does not affect how the data is loaded, which is exactly what I need.