topic Re: Adding Timestamp field to another timestamp field. in QlikView

Adding Timestamp field to another timestamp field.

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

Hi I want to add a timestamp field with another timestamp field.

Below is the situation:

Ex: a field named start_time has to be added with TOTAL_TIME FIELD.

Let's say start_time for a ticket is '7/13/2016 3:45:12 TT' and Total_Time field for the same is :2 days 3 hours 45 mins 12 sec.

So how can we add these 2 fields. (i.e. 7/13/2016 3:45:12 PM + 2 3:45:12) wherein 2 days 3 hours 45 min and 12 sec has been added to Start_time field giving the final result as : 7/15/2016 7:30:24 PM

How to achieve this addition ?

Re: Adding Timestamp field to another timestamp field.

vishsaggi — Fri, 12 Aug 2016 03:56:51 GMT

Kind of tricky, just a quick question is the Start_Time and Total_Time fields have the same format as you have mentioned ?

Re: Adding Timestamp field to another timestamp field.

Pulkit_Thukral — Fri, 12 Aug 2016 04:27:48 GMT

Yes Vish , field Start_time is the base time field with format 'M/D/YYYY hh:mm:ss TT'

Now, i have to add Total_time field into Start_time which has a format 'D hh:mm:ss'.

Basically the field Total_time field gives us total SLA Time duration.Hence, i want to add this duration to Start_time to create a custom field that describes SLA_Endtime.

I hope i made it a bit more clear.

Re: Adding Timestamp field to another timestamp field.

saikatghosh — Fri, 12 Aug 2016 05:01:57 GMT

Hi Pulkit,

try this

Time_Stamp:
LOAD
Timestamp(Timestamp#([Start Time],'M/D/YYYY hh:mm:ss TT')) as [Start Time],
Timestamp(Timestamp(Date(Timestamp(Timestamp#([Start Time],'M/D/YYYY hh:mm:ss TT')))+SubField([Total Time],' ',1))+
SubField([Total Time],' ',2)) as Total_SLA_Time
FROM D:\Saikat\Test.xls (biff, embedded labels, table is Sheet1$);

regards

Saikat

Re: Adding Timestamp field to another timestamp field.

jonathandienst — Fri, 12 Aug 2016 05:16:19 GMT

If you convert both fields on load to numeric Qlikview date values, then you can simply add the two together to get the end date. Something like:

LOAD *,

Date(start_time + Total_Time, 'M/D/YYYY hh:mm:ss TT') as SLA_Endtime

;

LOAD

Date#(start_time, 'M/D/YYYY h:mm:ss TT') as start_time,

Subfield(Total_Time, ' ', 1) + Subfield(Total_Time, ' ', 3) / 24 +

Subfield(Total_Time, ' ', 5) / (24*60) + Subfield(Total_Time, ' ', 7) / (24*60*60) as Total_Time,

...