Time

[Anonymous]

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?

[sunny_talwar · Accepted solution]

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;

[Anonymous]

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

[sunny_talwar]

I used an Inline load for just testing purposes.... you need to use your existing fact table to do this...

[Anonymous]

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