topic Re: Times in QlikView

Times

Wed, 16 Apr 2014 14:31:30 GMT

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

Re: Times

swuehl — Wed, 16 Apr 2014 17:39:59 GMT

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.

Re: Times

Thu, 17 Apr 2014 10:49:37 GMT

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

Re: Times

Thu, 17 Apr 2014 11:39:01 GMT

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

How do i do that?

Re: Times

Thu, 17 Apr 2014 15:42:47 GMT

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?

Re: Times

swuehl — Thu, 17 Apr 2014 17:41:06 GMT

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;