topic Re: Time and Loops in QlikView

Time and Loops

Fri, 28 Nov 2014 14:47:20 GMT

Hi all,

is there any way of calculating the duration between times?

So for example, for Day 3, a user logs in four times.

What I wanted to know is that between 02:15:02 and 02:32:24, what is the difference.

then I want to keep looping through the logins... so then what is the difference in hours and minutes between 02:32:24 and 09:24:37

So on and so forth...

Any ideas or tips?

Re: Time and Loops

datanibbler — Fri, 28 Nov 2014 14:53:33 GMT

Hi Sabah,

that is a date-time-stamp you have there. Those cannot be worked with directly in QlikView - first you have to split it into a date and a time, using sth. like

- DATE(Floor(num([stamp])), 'DD.MM.YYYY')

- TIME(Frac(num([stamp])), 'hh:mm:ss')

=> Then you can just subtract one time from the other and you'll get the difference.

HTH

Best regards,

DataNibbler

Re: Time and Loops

Fri, 28 Nov 2014 15:04:36 GMT

Hi Data Nibbler,

That works well, thank you. I am doing a calculation as such:

=hour(max(TimeTest) - min(TimeTest)) & ' hours ' & minute(max(LOG_TIME, Date) - min(LOG_TIME, Date)) & ' minutes'

I can now calculate the difference, but as you can see, its showing the total duration between 08:51:18 and 13:36:42.

It is not showing the difference between 08:51:18 and 09:10:41 AND THEN 09:10:41 and 13:36:42.

Re: Time and Loops

its_anandrjs — Fri, 28 Nov 2014 15:09:30 GMT

Hi,

In the load script load another table and with help of Interval function get the difference in the time for the first and last time stamp for the difference.

tmp:

LOAD Day,%Key,Timestamp(timestamp#(LOG_TIME,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as LOG_TIME,ID;

LOAD * Inline

[

Day,%Key,LOG_TIME,ID

3,13828,03/11/2014 02:15:02,1

3,13828,03/11/2014 02:32:24,1

3,13828,03/11/2014 09:24:37,1

3,13828,03/11/2014 10:24:38,1

];

LOAD

%Key,

Timestamp(Min(LOG_TIME)) as MinTime,

Timestamp(Max(LOG_TIME)) as MaxTime,

Interval(Timestamp(Max(LOG_TIME)) - Timestamp(Min(LOG_TIME)),'hh:mm:ss' ) as Diff

Resident tmp

Group By %Key;

Regards

Anand

Re: Time and Loops

its_anandrjs — Fri, 28 Nov 2014 15:20:11 GMT

Hi,

Even you can do this in the front end also no need to load another table for this take the straight table

Dim1:- Day

Dim2:- %Key

Expre1:- Timestamp(Min(LOG_TIME))

Expre2:- Timestamp(Max(LOG_TIME))

Expre3:- Interval(Timestamp(Max(LOG_TIME)) - Timestamp(Min(LOG_TIME)),'hh:mm:ss')

Regards

Anand

Re: Time and Loops

Fri, 28 Nov 2014 15:23:04 GMT

Thanks all 🙂

Re: Time and Loops

its_anandrjs — Fri, 28 Nov 2014 15:27:55 GMT

Hi,

Thanks, if got correct answer close the thread with appropriate answers.

Regards

Anand

Re: Time and Loops

MarcoWedel — Fri, 28 Nov 2014 15:29:47 GMT

create an additional expression:

Interval(LOG_TIME - Above(LOG_TIME))

hope this helps

regards

Marco