topic Re: Time in App Development

Time

Anonymous — Thu, 01 Jun 2017 12:07:29 GMT

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?

Re: Time

sunny_talwar — Thu, 01 Jun 2017 12:30:39 GMT

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;

Re: Time

Anonymous — Thu, 01 Jun 2017 13:53:01 GMT

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

sunny_talwar — Thu, 01 Jun 2017 13:56:03 GMT

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

Re: Time

Anonymous — Thu, 01 Jun 2017 14:09:37 GMT

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