Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am thoroughly confused by this problem, and need some help to understand why Qlik Sense is behaving like it is! I apologise if this post gets a bit lengthy, but I've tried to include just the necessary parts as it applies to my use case. This only happens for this specific example.
I have a set of data stored in 'test.tsv':
starttime | endtime |
2019-07-18T16:45:31+10:00 | 2019-07-18T16:52:00+10:00 |
2019-07-18T16:54:28+10:00 | 2019-07-18T17:01:00+10:00 |
2019-07-18T17:03:27+10:00 | 2019-07-18T17:10:00+10:00 |
I want to measure the time between a given endtime and the next starttime. To do this, I have the following data load script:
InitialTable:
LOAD
"starttime",
"endtime"
FROM [lib://source/test.tsv]
(txt, utf8, embedded labels, delimiter is '\t', msq);
TableFullProcessed:
LOAD
RecNo() as [ID],
timestamp(timestamp#(replace(left("starttime",19), 'T', ' '),'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') as "start time",
timestamp(timestamp#(replace(left("endtime",19), 'T', ' '),'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') as "end time"
Resident InitialTable;
left Join
ElapsedTable0:
LOAD
[ID],
Peek([start time], rowno(), 'TableFullProcessed') as "next start time"
resident TableFullProcessed;
left Join (TableFullProcessed)
ElapsedTable1:
LOAD
ID,
if([ID] <> Peek([ID], -1, 'TableFullProcessed'), interval([next start time]-[end time],'mm:ss'), null()) as "time between"
resident TableFullProcessed;
drop table InitialTable;
Running this, the table TableFullProcessed appears to have correctly calculated the times.
However when I go to my visualisation a table I'm using to analyse it has the results:
Which is clearly not correct. This sometimes happens when graphing on a bar chart, but it is less consistent. Why is this happening? I want my visualisations to be in minutes and seconds (mm:ss).