QlikView Time Difference in Hours

[#1]

Hi,

I have a pivot table like this as attached.

I want to add 3 expression to the Chart  like start, end and total.

Start = roadnetstart - dddriverstart (roadnetstart  is 9.30 am and dddriverstart  is 7.00 am. i  want Start as 2.30 hr)

End = roadnetend - dddriverend

Total = Start + End

i have made roadnet strt as right(Timestamp(ACTUAL_START-7/24),10)

DriverStrat as DDDriverStart

Is making three expression by using this is possible. Please let me know.

[swuehl]

Yes, it should be possible to just use simple maths on your time fields, but only if your fields have a numeric representation.

Please check also

http://community.qlik.com/docs/DOC-3102

If you use something like Right(Timestamp(ACTUAL_START-7/24),10) chances are that you only get a string back, not a QV timestamp or time with a numeric representation.

I think your DDDriverStart field is just a pure text field (you can see this by its default left alignment, or by checking the field tags in table viewer), so you can use this field in your time difference calculations as is.

If you could post a small sample app or at least some sample input data, we can probably help you with the syntax.

Regards,

Stefan

[#3]

Swuehl,

Sorry for the delay of Sample Data. I have attached a Excel file thats my sample data. Please have a look on that

[CELAMBARASAN]

Check with interval function and try something like this

start=Interval(Time#([Roadnet Start], 'h:mm:ss tt') - Time#(DDDriverStart, 'h:mmtt'), 'hh:mm')

end =Interval(Time#([Roadnet End], 'h:mm:ss tt') - Time#(DDDriverEnd, 'h:mmtt'), 'hh:mm')

Total = Time(Interval(Time#([Roadnet Start], 'h:mm:ss tt') - Time#(DDDriverStart, 'h:mmtt'), 'hh:mm') + Interval(Time#([Roadnet End], 'h:mm:ss tt') - Time#(DDDriverEnd, 'h:mmtt'), 'hh:mm'), 'hh:mm')

[#5]

Sorry,

It wont give me the output...

[swuehl]

Please have a look at attached proposal.

[#7]

I have created the dimension like this

DDDriverStart - Directly pulled from table

DDDriverEnd- Directly pulled from table

Roadnet Start = Time(right(Timestamp(ACTUAL_START-7/24),10),'h:mm:ss TT')

Roadnet End=Time(right(Timestamp(ACTUAL_END-7/24),10),'h:mm:ss TT')

And now i want to make some expressions like Start, End and Total

.

Start = roadnetstart - dddriverstart (Need in hh mm format)

End = roadnetend - dddriverend (Need in hh mm format)

Total = Start + End

[#8]

Hi,

I was not able to open the document

[swuehl · Accepted solution]

Are you using a personal edition of QlikView or did the opening failed with an error?

Anyway, this is the script used in my sample:

LOAD *,

interval(Start+End) as Total;

LOAD *,

interval([Roadnet Start] - DDDriverStart) as Start,

interval([Roadnet End] - DDDriverEnd) as End;

LOAD Location,

     DRName,

     MainDate,

     TMRoute,

     time(time#(DDDriverStart,'hh:mmTT')) as DDDriverStart,

     time(time#(DDDriverEnd,'hh:mmTT')) as DDDriverEnd,

     time(time#([Roadnet Start],'hh:mm:ss TT')) as [Roadnet Start],

     time(time#([Roadnet End],'hh:mm:ss TT')) as [Roadnet End]

FROM

CH01_20131007_030610.xls

(biff, embedded labels, table is [Sheet1$]);

[#10]

I am using qlikview server version. Its failed with an error.

I will go through it and update you.

Thanks a lot!!!