Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

alakazam
New Contributor III

Time

I have three columns. In first I have "item", in second I have time (format is Hour:MinuteSmiley Frustratedecond), 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?

1 Solution

Accepted Solutions

Re: Time

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;

4 Replies

Re: Time

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;

alakazam
New Contributor III

Re: Time

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

Re: Time

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

alakazam
New Contributor III

Re: Time

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