Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

calculating number of minutes between incoming hour and '16:15'


Hello,

I am trying to calculate the number of minutes between an incoming timestamp e.g. '

31-dec-14 08:05:00

and the fixed hour '16:15'

Defined some calculations in the load script:

    TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

    MakeTime(16,15) as Cutoff_time,

    INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

    INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

    TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format

Testresult "interval static" returns what I expected (240 min), but when trying to apply the same logic to my real time in, i am unable to get the correct answer.

Any ideas on how to do this? Thanks for your help and time. Capture.PNG

18 Replies
jagan
Luminary Alumni
Luminary Alumni

Try like this

LOAD

*,

(MakeTime(16,15) - (Frac(TimeFieldName)  * 24 * 60) AS DurationInMinutes

FROM DataSource;

Not applicable
Author

Thanks again for your reply. Modified the script:


  MakeTime(16,15) - (Frac( "Sessie - OK zaal in tijdstip" )  * 24 * 60) AS DurationInMinutes

but the results are both negative and of a high absolute Capture.PNGvalue :

Not applicable
Author

think the brackets might not be right - also not sure MAKETIME(16,15) is returning just a time.

perhaps try:

(FRAC(MAKETIME(16,15)) - FRAC("Sessie - OK zaal in tijdstip")) * 24 * 60

Not applicable
Author

Thanks for your reply. Result did change but i am still not able to fully retrieve the same result as in the interval static column (negative and low value now)

Capture.PNG

swuehl
MVP
MVP

Still believe that the first answers in this thread as well as Andrew's expression should work (first answers given the format code is correct or can be left out if the field is automatically interpreted as timestamp).

Could you please post your currently used load statement?

The last screenshot looks strange, like you've added a row number or something like this on top of a small value.

Not applicable
Author

Hello. Thanks for your reply. Here is the load statement where i tried to include all the ideas :

"Sessie - OK zaal in tijdstip"  as Inputfield,

    TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

    MakeTime(16,15) as Cutoff_time,

    INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

    INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as ownidea,

    Interval((Time#('16:15', 'hh:mm') - Frac("Sessie - OK zaal in tijdstip")), 'hh:mm:ss') as anwer1,

    interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) as answer2,

    (MakeTime(16,15) - Frac(Date#("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss')))  * 24 * 60 as answer3,

    (MakeTime(16,15) - Frac(Date("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss')))  * 24 * 60 as answer4,

    (FRAC(MAKETIME(16,15)) - FRAC("Sessie - OK zaal in tijdstip")) * 24 * 60 as answer5

    

    The results can be seen on the screenshot. Any suggestions are still most welcome.Capture.PNG

sunny_talwar

Can you give this a shot:

Interval((Time#('16:15', 'hh:mm') - Frac(Date#("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))), 'hh:mm:ss') as anwer6,

swuehl
MVP
MVP

Answer 1, 4 and 5 look quite promising. I think you may be confused by comparing first line of a list box to first line of another listbox. These lines don't need to come from the same record, I think they are just sorted numeric.

I would suggest selecting a value and then compare the created field values that are really related to each other.

Not applicable
Author

Your suggestion did work. In debug-mode I uploaded only 1 record and the result does indeed make sense. Many thanks to all for your time and ideas.

Capture.PNG