Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Pulkit_Thukral
Partner
Partner

Adding Timestamp field to another timestamp field.

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 ?

4 Replies
vishsaggi
Champion III
Champion III

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
Partner
Partner
Author

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
Contributor III
Contributor III

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
Partner
Partner

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,

     ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein