Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have three columns. In first I have "item", in second I have time (format is Hour:Minute:Second), third I have date.
Something like:
A 23:58:56 2017-05-21
A 23:59:08 2017-05-21
A 00:02:45 2017-05-22
A 00:02:59 2017-05-22
B 00:03:05 2017-05-22
etc.
And I would like result, where will be new column with result:
A 23:58:56 2017-05-21 00:00:00
A 23:59:08 2017-05-21 00:00:12
A 00:02:45 2017-05-22 00:03:37
A 00:02:59 2017-05-22 00:00:14
B 00:03:05 2017-05-22 00:00:00
etc.
I need get time duration in seconds between times for each Item, and whit each new item I want start from 0. Anybody have a idea for right formula?
May be this
Table:
LOAD *,
TimeStamp(Date+Time) as TimeStamp;
LOAD * INLINE [
Dim, Time, Date
A, 23:58:56, 2017-05-21
A, 23:59:08, 2017-05-21
A, 00:02:45, 2017-05-22
A, 00:02:59, 2017-05-22
B, 00:03:05, 2017-05-22
];
FinalTable:
LOAD *,
Interval(If(Dim = Previous(Dim), TimeStamp - Previous(TimeStamp), 0), 'hh:mm:ss') as Diff
Resident Table
Order By Dim, TimeStamp;
DROP Table Table;
May be this
Table:
LOAD *,
TimeStamp(Date+Time) as TimeStamp;
LOAD * INLINE [
Dim, Time, Date
A, 23:58:56, 2017-05-21
A, 23:59:08, 2017-05-21
A, 00:02:45, 2017-05-22
A, 00:02:59, 2017-05-22
B, 00:03:05, 2017-05-22
];
FinalTable:
LOAD *,
Interval(If(Dim = Previous(Dim), TimeStamp - Previous(TimeStamp), 0), 'hh:mm:ss') as Diff
Resident Table
Order By Dim, TimeStamp;
DROP Table Table;
It work, thank you very much Sunny.
Only I dont understand this part:
LOAD * INLINE [
Dim, Time, Date
A, 23:58:56, 2017-05-21
A, 23:59:08, 2017-05-21
A, 00:02:45, 2017-05-22
A, 00:02:59, 2017-05-22
B, 00:03:05, 2017-05-22
];
So I skip it. And maybe it is reason why Qlik wrote me a error, that it dont know Table. I have many more datas. Then I skiped as well row with drop table and it work. I know that isnt good solution, but it work and it is important for me. Thank you again. You are really Qlik wizzard. 🙂
I used an Inline load for just testing purposes.... you need to use your existing fact table to do this...
I see, I didnt know right function of "inline load" and some others details. Im beginner with Qlik Sense and as bonus I have terrible english, so I dont understand each part of Qlikhelp.
Sure, I did it and it work. Thank you for your help Sunny. 🙂