Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
firoz24
Creator
Creator

Interval hours

 Hi all,

I am trying to get the interval hours between my field and current timestamp 

Below I have attached the script :-

Interval(Now(1) - Timestamp#([SYSDATE], 'YYYY-MM-DD hh:mm:ss [fff] TT'), 'hh') as Difference_In_Hours,

Interval(Now(1) - Timestamp(Timestamp#(SYSDATE, 'YYYY-MM-DD hh:mm:ss TT')), 'hh') as Difference_In_Hours1,

SYSDATE  Field format  - 2024-06-04 09:23:58. AM 

If i have get the interval hours I will use it in set analysis 

For eg:- need to count only the VIN (field) which is [Difference in Hours] greater than 16 hours 

 

 

1 Solution

Accepted Solutions
firoz24
Creator
Creator
Author

Solved my issue,

Thank you Tresesco and vegar

View solution in original post

8 Replies
tresesco
MVP
MVP

Looking at the sample value, your format has to be corrected, like:
'YYYY-MM-DD hh:mm:ss.[fff] TT'

Note : blank space, dot - all matter

firoz24
Creator
Creator
Author

Hi Tresesco Actually in my sql db the SYSDATE Field Format - 2020-11-07 16:52:00.000

So my set format in qlik - SET TimeFormat='hh:mm:ss.fff';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss.[fff]';

 

So check my changed script again:--

Now(1) as Current_timestamp,

0-Ceil(Interval(Now(1) - Timestamp#([SYSDATE], 'YYYY-MM-DD hh:mm:ss'),'hh')) as Difference_In_Hours,

Interval(Now(1) - Timestamp(Timestamp#(SYSDATE, 'YYYY-MM-DD hh:mm:ss')),'hh') as Difference_In_Hours1,

 

Please check what is the issue 

I have attached the image too

firoz24_0-1724135617883.png

 

 

 

 

Vegar
MVP
MVP

It looks like both fields are recognized ad timestamps. There should be no need to use the timestamp#() function on them.

Try without using timestamp#()

firoz24
Creator
Creator
Author

So you mean I remove the timestamp ?

Interval(Now(1) - Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss')),'hh' as Difference_In_Hours1,

But i am getting data model like this

firoz24_0-1724136035874.png

 

tresesco
MVP
MVP

Field values are right-aligned in the listbox, that means they are being treated as numeric. Hence, you can get rid of parsing function, and try like:
Interval(Now(1) - SYSDATE,'hh') as Difference_In_Hours1

firoz24
Creator
Creator
Author

Hi 

I am getting like this now 

firoz24_0-1724136982426.png

Interval(Now(1) - SYSDATE,'hh') as Difference_In_Hours1,

if ([Difference_In_Hours1] >= 08 ,'1','0') as Final_Difference_hours,

I hope this is correct but still in set analysis the KPI Vallue i am getting is wrong

Please check 

 

tresesco
MVP
MVP

If you want to compare with 8 hours, then you have to write like:

if ([Difference_In_Hours1] >= 8/24 ,1,0) as Final_Difference_hours

Because, 1 hour = 1/24 (in number)

Also, in your script, better use 'h' instead of 'hh' to avoid additional 0 preceding 

firoz24
Creator
Creator
Author

Solved my issue,

Thank you Tresesco and vegar