Adding Timestamp field to another timestamp field.

[Pulkit_Thukral]

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 ?

[vishsaggi]

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

[Pulkit_Thukral]

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.

[saikatghosh]

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

[jonathandienst]

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,

     ...