Discussion Board for collaboration related to QlikView App Development.
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.
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$);
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:
Date(start_time + Total_Time, 'M/D/YYYY hh:mm:ss TT') as SLA_Endtime
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,