
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date Difference
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
- Subscribe by Topic:
-
Data Load Editor
-
Developers
-
expression
-
General Question
-
Script
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
If you want to go quickly, go alone. If you want to go far, go together.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yeah , i did the same and got the solution . Thanks Everyone.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
If you want to go quickly, go alone. If you want to go far, go together.
