Date Difference

[RajaRajput28]

Hi, 

I am new to Qliksesne. For 1 of my requirement I need to get total duration for a task. Date format is bit weird here and i need to follow the same. Please help me in getting the time duration for the same. 

Start Date = 

20211028T123817.714 GMT

End Date = 

20211028T123912.026 GMT

My expectation is to get output as Start date - End Date but unable to get desired result . let me know if you need more details 

Thanks,

Raja 

[RajaRajput28]

@sunny_talwar 

[Or]

To get the difference between two timestamps, you can simply subtract one from the other (which you can then format with e.g. Interval() if desired). However, you must first convert your fields, which look to be textual, into valid timestamps. You can probably do this easiest by using Left(8) and Mid(10,10) within conversion functions, replacing the numeric values I used below:

Load Timestamp(Date#('20211028','YYYYMMDD') + Time#('123817.714','hhmmss.fff'))
autogenerate(1);

[HugoRomeira_PT]

Hello,

First thing you need to do, is to make sure that you convert the timestamp into a format that Qlik understand it as such.

You can do this using the function Timestamp#()

After this you will be able to subtract and if you want to present the results in time format, or calculate in seconds, minutes, hours, days, etc.

Bellow you will find the script with the solution:

Set vStart= '20211028T123817.714 GMT';

Set vEnd = '20211028T123912.026 GMT';

Test:
LOAD *,
Time(End_Transformed - Start_Transformed,'hh:mm:ss.fff') as [Duration in Time],
(End_Transformed - Start_Transformed)*24*60*60 as [Duration in Seconds];

LOAD Date(Timestamp#(left('$(vStart)',19),'YYYYMMDDThh:mm:ss.fff'),'YYYY-MM-DD hh:mm:ss.fff') as Start_Transformed,
Date(Timestamp#(left('$(vEnd)',19),'YYYYMMDDThh:mm:ss.fff'),'YYYY-MM-DD hh:mm:ss.fff') as End_Transformed,
'$(vStart)' as Start_Original,
'$(vEnd)' as End_Original
AutoGenerate(1);

Hope it helps!

[vinieme12]

As below

FrontEnd

interval(
timestamp#(subfield('20211028T123912.026 GMT',' GMT',1),'YYYYMMDDThhmmss.fff') // End - Start
-
timestamp#(subfield('20211028T123817.714 GMT',' GMT',1),'YYYYMMDDThhmmss.fff')
)

in Script

let start = timestamp#(subfield('20211028T123912.026 GMT',' GMT',1),'YYYYMMDDThhmmss.fff');
let end =timestamp#(subfield('20211028T123817.714 GMT',' GMT',1),'YYYYMMDDThhmmss.fff');

let interval = interval('$(end)'-'$(start)') ;

Trace $(interval);

[RajaRajput28]

Thanks All for the response , @vinieme12  : I Tried your frontend solution it's working but it's more like hard coded . i have various jobs and Start time and End Time differs. i won't be able to add manual time for each job. Instead of adding the time , i tried it with giving YYYYMMDDThhmmss.fff but it didn't worked for me

[vinieme12 · Accepted solution]

You can use the same and replace the string with field name

Fact:

Load  JobNo,JobStart,JobEnd

,interval(
timestamp#(subfield(JobEnd,' GMT',1),'YYYYMMDDThhmmss.fff')

-
timestamp#(subfield(JobStart,' GMT',1),'YYYYMMDDThhmmss.fff')
) as Jobduration

From SomeData;

[RajaRajput28]

yeah , i did the same and got the solution . Thanks Everyone.

[HugoRomeira_PT]

Hello,

Apologize if it was not clear, but you just needed to replace the variables '$(vStart)' and $(vEnd) by the proper field names.

Test:
LOAD *,
Time(End_Transformed - Start_Transformed,'hh:mm:ss.fff') as [Duration in Time],
(End_Transformed - Start_Transformed)*24*60*60 as [Duration in Seconds];

LOAD Date(Timestamp#(left(fieldnameStart,19),'YYYYMMDDThh:mm:ss.fff'),'YYYY-MM-DD hh:mm:ss.fff') as Start_Transformed,
Date(Timestamp#(left(fieldnameEnd,19),'YYYYMMDDThh:mm:ss.fff'),'YYYY-MM-DD hh:mm:ss.fff') as End_Transformed,

...

My advise is that you do it in the load script editor to ensure performance and avoid future calculations.