Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
ryan_evans1984
New Contributor

Calculate the difference in seconds between two time stamps.

I have used a resident load group by with in my code and I cannot calculate the correct duration in seconds between the start timestamp and end time stamp

Current_Data:

Load

datStart,

datEnd,

""Looking for a line to calculate the difference between datStart & datEnd ******

MachineName as L_Machine_Name

RESIDENT Data

Group by MachineName;

8 Replies
Highlighted

Re: Calculate the difference in seconds between two time stamps.

There is no aggregate here, Not sure why you required Group By? Perhaps this.

Current_Data:

Load

datStart,

datEnd,

Interval(datStart-dayEnd,'hh:mm:ss') as NewField,

MachineName as L_Machine_Name

RESIDENT Data

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
vishsaggi
Esteemed Contributor III

Re: Calculate the difference in seconds between two time stamps.

Can you tell us the date format your fields has.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Calculate the difference in seconds between two time stamps.

Assuming datEnd and datStart are timestamps, the differences in seconds would be:

num(interval(datEnd-datStart, 's'))

You may perhaps not want the actual seconds, but rather keep the standard duration (1 = 1 day) and instead just display the value as seconds.

interval(datEnd-datStart, 's')


-Rob

Highlighted
ryan_evans1984
New Contributor

Re: Calculate the difference in seconds between two time stamps.

Thanks  for the comments.

I created a group by because I wanted a small table by machine... the main table 'data' has 800k rows.

I just wanted to show the last datstart and datend by machine in a small table.

Highlighted
ryan_evans1984
New Contributor

Re: Calculate the difference in seconds between two time stamps.

Thanks  for the comments.

I created a group by because I wanted a small table by machine... the main table 'data' has 800k rows.

I just wanted to show the last datstart and datend by machine in a small table.

Highlighted
ryan_evans1984
New Contributor

Re: Calculate the difference in seconds between two time stamps.

Thanks for the comments.

The num(interval(datEnd-datStart, 's')) works and displays a number but I wanted seconds.


And the interval(datEnd-datStart, 's') doesnt work.


tried multiple things and still cannot get this into seconds.


Highlighted
MVP & Luminary
MVP & Luminary

Re: Calculate the difference in seconds between two time stamps.

I typed too fast.

interval(datEnd-datStart, 's')


should display the value in seconds. What does it display for you?


-Rob

Highlighted
balabhaskarqlik
Honored Contributor

Re: Calculate the difference in seconds between two time stamps.

May be:

=sum(datEnd-datStart) * 24 * 60 * 60