topic Re: Re: QlikView Time Difference in Hours in QlikView

QlikView Time Difference in Hours

Fri, 04 Oct 2013 10:35:41 GMT

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.

Re: QlikView Time Difference in Hours

swuehl — Fri, 04 Oct 2013 14:26:50 GMT

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

Re: Re: QlikView Time Difference in Hours

Mon, 07 Oct 2013 10:28:57 GMT

Swuehl,

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

Re: QlikView Time Difference in Hours

CELAMBARASAN — Mon, 07 Oct 2013 10:39:57 GMT

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')

Re: QlikView Time Difference in Hours

Mon, 07 Oct 2013 11:23:53 GMT

Sorry,

It wont give me the output...

Re: Re: QlikView Time Difference in Hours

swuehl — Mon, 07 Oct 2013 11:34:33 GMT

Please have a look at attached proposal.

Re: QlikView Time Difference in Hours

Mon, 07 Oct 2013 11:36:21 GMT

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

Re: QlikView Time Difference in Hours

Mon, 07 Oct 2013 11:50:54 GMT

Hi,

I was not able to open the document

Re: QlikView Time Difference in Hours

swuehl — Mon, 07 Oct 2013 11:57:37 GMT

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$]);

Re: QlikView Time Difference in Hours

Mon, 07 Oct 2013 12:01:00 GMT

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

I will go through it and update you.

Thanks a lot!!!

Re: QlikView Time Difference in Hours

Tue, 08 Oct 2013 11:07:30 GMT

Thanks Swuhl, it worked