Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Kind of tricky, just a quick question is the Start_Time and Total_Time fields have the same format as you have mentioned ?
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.
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
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,
...