Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try like this
LOAD
*,
(MakeTime(16,15) - (Frac(TimeFieldName) * 24 * 60) AS DurationInMinutes
FROM DataSource;
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 value :
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
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)
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.
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.
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,
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.
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.