topic Re: Subtract Timestamp in qlikview in QlikView

Subtract Timestamp in qlikview

balanandam — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to covert the values to number, what I'm doing is ?

interval(timestamp('08/08/2017 14:12:01','DD/MM/YYYY hh:mm:ss')-timestamp('08/08/2017 14:15:47','DD/MM/YYYY hh:mm:ss'),'s') AS DURATION

it gives 226. But when I export in excel, it showing as text not the number, seems, bcz of this I cant roll up values and leads wrong figures. Can any one give solution for this ?

And, what is the difference between num() &num#() ?

Thanks!

Balanandam

Re: Subtract Timestamp in qlikview

Miguel_Angel_Baeyens — Tue, 31 Oct 2017 15:55:32 GMT

Wrap the whole Interval() function within a Num() function and check with Qlik Support. Interval returns a numeric value and if it does not, then it should be checked.

The difference between any function with a # at the end like Num#() Date#() or Interval#() and their equivalents without the "#" is that the former interpret values while the latter represent values. By default, any value in QlikView has a numeric and string representation, clear example of which is "000002". It could be the number 2, or the string "000002".

Num#(000002) will work and will return the string with the leading zeros, while Num('000002') will also work and will return 2, as the leading zeros are not significant for a numeric value.

Said in a less proper way (incorrect actually but for the sake of the example), "#" functions display while not "#" functions convert.

Re: Subtract Timestamp in qlikview

balanandam — Tue, 31 Oct 2017 18:03:32 GMT

Thanks for reply.

I have the data like below

Dateswork:

LOAD * INLINE

[

Date,No,StartDate,EndDate

'23/10/2017',1,'23/10/2017 12:04:51','23/10/2017 13:09:27'

'23/10/2017',1,'23/10/2017 11:12:09','23/10/2017 11:34:01'

'26/10/2017',2,'26/10/2017 15:47:17','26/10/2017 15:56:26'

'23/10/2017',1,'23/10/2017 13:00:31','23/10/2017 13:00:35'

'20/10/2017',3,'20/10/2017 15:08:49','20/10/2017 15:08:54'

'21/10/2017',4,'21/10/2017 17:58:49','21/10/2017 18:16:21'

'25/10/2017',5,'25/10/2017 14:27:26','25/10/2017 14:40:05'

'26/10/2017',6,'26/10/2017 19:58:36','26/10/2017 20:27:37'

];

I need to derive duration in sec (must be in number format)for this startdate and enddate.

can you share the sample .qvw with this data ?

Thanks in advance!

Re: Subtract Timestamp in qlikview

rwunderlich — Tue, 31 Oct 2017 18:17:18 GMT

In the expression you posted, you should be using the timestamp#() function, not timestamp().

-Rob