topic Re: Duplicate timestamps in QlikView

Duplicate timestamps

terrusie — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Duplicate timestamps

sunny_talwar — Wed, 23 May 2018 14:38:45 GMT

May be something like this

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

Re: Duplicate timestamps

sunny_talwar — Wed, 23 May 2018 14:46:44 GMT

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

];

Re: Duplicate timestamps

terrusie — Wed, 23 May 2018 14:46:50 GMT

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.