Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Times

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

5 Replies
swuehl
MVP
MVP

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.

Not applicable
Author


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

Not applicable
Author

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

How do i do that?

Not applicable
Author

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
MVP
MVP

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;