Times

[#1]

I have a table of times for a journey and I want to calculate the frequency between stops A & B between Town and City along wit a few assocaited summary totals

The spreadsheet attached shows the data on the left hand sidei n Blue (columns A to D) 

The output I need is shown n the same spreadsheet on the RHs in yellow

[swuehl · Accepted solution]

Maybe like attached.

Prepare the actual frequency and deviation in your script, then getting the tables with the statistics is straight forward.

You may need to order your input table by Stop, Destination and ArrivalTime to get the peek() calculations right.

[#3]

Thats great thank you.. It works but so I have an understanding of why could you briefly comment on what some of the formulae does specifically the two bits underlined...

*,

  if(Variation >= maketime(0,1) ,dual('Yes',1),dual('No',0)) as OneMinuteOrGreater;

LOAD *,

  interval(ActFrequency - [Scheduled Frequency]) as Variation;

LOAD Stop,

     Destination,

     ArrivalTime,

     [Scheduled Frequency],

     if(peek(Stop)=Stop and peek(Destination)=Destination,interval( ArrivalTime-peek(ArrivalTime))) as ActFrequency

FROM



(ooxml, embedded labels, table is Sheet1);

[#4]

In hindsight I think i need to order I think I  need to order my input input table.

How do i do that?

[#5]

The script just looks at the previous time in the load,

My source data isnt actually in Stop order

How do i get the script to look at the previous time for the specific stop from and stop to?

[swuehl]

Hi Glenn,

maybe start with these three blog posts from Henric, should answer most of you questions:

QlikView Date fields

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual    

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/16/counters-in-the-load

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/08/peek-vs-previous-when-to-use-each

Also look at the order by clause in the HELP or reference manual. You can use the order by when loading e.g. from a resident table or a DB source, so if your data is not properly sorted, you may first load it as is, then use my LOAD using a RESIDENT LOAD:

INPUT:

LOAD Stop,

     Destination,

     ArrivalTime,

     [Scheduled Frequency]

FROM

[HeadwayExample.xlsx]

(ooxml, embedded labels, table is Sheet1);

RESULT:

LOAD *,

  if(Variation >= maketime(0,1) ,dual('Yes',1),dual('No',0)) as OneMinuteOrGreater;

LOAD *,

  interval(ActFrequency - [Scheduled Frequency]) as Variation;

LOAD *,

     if(peek(Stop)=Stop and peek(Destination)=Destination,interval( ArrivalTime-peek(ArrivalTime))) as ActFrequency

Resident INPUT order by Stop, Destination, ArrivalTime;

drop table INPUT;