Duplicate timestamps

[terrusie]

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).

Start	Finish	Task
05/01/2018 06:01	NULL	Task 2
05/01/2018 06:01	05/01/2018 06:01	Task 1

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

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

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).

[sunny_talwar]

May be something like this

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

[sunny_talwar]

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]

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.