Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
etrotter
Creator II
Creator II

Time Interval nulls

Hi Qlikers, 

I wrote this interval function to subtract two dates and times. For the most part, it is working, but for some reason, it is throwing out a null value for every time value: 

Interval(Timestamp(corDate&' '&corTime)-Timestamp(date(date,'YYYY-MM-DD')&' '&ime),'hh:mm:ss')as Corrected_Interval,

 
IDimecorTimeCorrected_Interval
    
2684981008:54:3308:54:400:00:07
2684981008:54:3308:54:40-
2684980908:54:3308:54:400:00:07
2684980908:54:3308:54:40-
2684980808:54:3308:54:400:00:07
2684980808:54:3308:54:40-
 

I've tried to add a where not isnull(Interval(Timestamp(corDate&' '&corTime)-Timestamp(date(date,'YYYY-MM-DD')&' '&ime),'hh:mm:ss')) to the script but still haven't been able to remove the null values. Is there something wrong with my formula? 

Labels (2)
1 Reply
jaibau1993
Partner - Creator III
Partner - Creator III

Hi!

I think you may be confusing interpretation and format functions. I'd try something like

Interval(   
   (corDate+Time#(corTime, 'hh:mm:ss'))
   -
   (date+Time#(ime, 'hh:mm:ss') 
)
as Corrected_Interval

Where I supposed that corDate and date are properly interpreted by Qlik as dates. I ensure Qlik interprets corTime and ime as times using Time# function (it is an interpretation function, check docs!)

Regards,

Jaime.