Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
in Qlikview I use the following expression:
time(interval(.timeAdmin.timeArrive),'mm')
but when the arrival time is BEFORE the Admin time I get an odd answer:
AppointmentDate timeAdmin ArrivedTime admit - arrival
4/08/2017 14:30:00 14:30:00 47 ####### -13
47 IS INCORRECT I can see that (47 - 60) would give -13
but how do I do it in Qlikview?
I am getting the dates from 2 different systems - I think the format is the same (originally it was a problem because one showed AM and the other did not): Then normally it would be a full time stamp but not in one of the systems!
time(AdmissionDateTime,'hh:mm:00') as timeAdmin, |
time(ArrivedTime,'hh:mm:00') as timeArrive, |
This was painful to do even in Excel!
Thank you!
Jo
Please share the image where you getting wrong values while subtract?
should show in the excel worksheet?
In Qlikview the answer shows as column 4 - i have highlighted the problem rows.
You're generating a value of -0.009 or so (AKA -13 minutes). Formatting negative values as time is a little quirky, but the result is probably 23:47:00 in this case.
It seems like what you actually need is Interval(timeAdmin-timeArrive).
Then, Use this?
=Time(Interval(FieldName, FieldName2), 'MM')
Update
I actually get the answer of 47 in Qlikview - I get the answer of -13 only in Excel.
I used the formula: time(interval(.timeAdmin.timeArrive),'mm')
but the expression you suggested gives me the same thing ... no negatives ...
Why are you formatting the interval as a time part?
If you're trying to convert the value into minutes, that's probably not the right way to go about it. Try:
(TimeAdmin - TimeArrive)*(24*60)
Obviously you can just multiply by 1440, I left it verbose to make it clear I was multiplying by hours and minutes. This will convert the value (which is stored as days) into minutes.
In your case I don't think you need the interval try directly
(TimeAdmin - TimeArrive)*(24*60)
Hi,
try this formula :
if( CDU.CDU_TTT.PatientArrivedTime>IMP.timeAdmin,-1*minute(Timestamp( CDU.CDU_TTT.PatientArrivedTime-IMP.timeAdmin,'mm')), minute(Timestamp(IMP.timeAdmin- CDU.CDU_TTT.PatientArrivedTime,'mm'))) as INTERVAL
I'm not sur it's the easiest way to do it but it should work !
Hi Anne
no the same thing ... 47 should be -13 ... when timeAdmin is > ArrivedTime it needs to be taken away from 60. I suppoise i should do an if statement?
Jo