Time and Loops

[#1]

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?

[datanibbler]

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

[#3]

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.

[its_anandrjs]

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

[its_anandrjs]

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

[#6]

Thanks all 🙂

[its_anandrjs]

Hi,

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

Regards

Anand

[MarcoWedel]

create an additional expression:

Interval(LOG_TIME - Above(LOG_TIME))

hope this helps

regards

Marco