Run Times

[#1]

Hi I need to find a way of calculating the journey time of a run from ASW to NIS. This is a simple calculation:

(ActDepTime ASW) - (ActArrTime NIS)  but there are a number of different services and I need to do multiple calcuations on the same set of data (one calculation for each service). In this example there are three services  01-02-050-07, 01-02-050-08 & 01-02-050-09. Im new to this.. Any ideas anyone?

ServiceID	Stop	ActArrTime	ActDepTime
01-02-050-07	ASW	06:01:58	06:02:48
01-02-050-07	ASM	06:05:36	06:06:22
01-02-050-07	AUD	06:08:18	06:10:15
01-02-050-07	DYL	06:12:18	06:13:21
01-02-050-07	CER	06:14:41	06:15:35
01-02-050-07	EDL	06:16:50	06:17:43
01-02-050-07	CLA	06:19:40	06:20:33
01-02-050-07	VEL	06:22:51	06:23:35
01-02-050-07	SPC	06:24:58	06:25:32
01-02-050-07	HOT	06:26:40	06:27:18
01-02-050-07	NIS	06:28:36	06:29:27
01-02-050-08	ASW	06:14:34	06:15:09
01-02-050-08	ASM	06:17:30	06:17:50
01-02-050-08	AUD	06:19:35	06:21:17
01-02-050-08	DYL	06:23:19	06:24:36
01-02-050-08	CER	06:26:12	06:27:09
01-02-050-08	EDL	06:28:45	06:29:37
01-02-050-08	CLA	06:31:35	06:32:23
01-02-050-08	VEL	06:34:13	06:35:02
01-02-050-08	SPC	06:36:27	06:37:05
01-02-050-08	HOT	06:38:15	06:38:52
01-02-050-08	NIS	06:40:19	06:41:01
01-02-050-09	ASW	06:26:36	06:27:18
01-02-050-09	ASM	06:29:31	06:30:13
01-02-050-09	AUD	06:31:59	06:34:20
01-02-050-09	DYL	06:36:16	06:37:14
01-02-050-09	CER	06:38:29	06:39:12
01-02-050-09	EDL	06:40:18	06:40:58
01-02-050-09	CLA	06:42:36	06:43:22
01-02-050-09	VEL	06:45:13	06:46:15
01-02-050-09	SPC	06:47:56	06:48:34
01-02-050-09	HOT	06:50:04	06:50:54
01-02-050-09	NIS	06:52:08	06:52:51

[jvitantonio]

Hi,

Please find the solution in the attached app.

I hope it helps.

JV

BI Experience | A place to share our Business Intelligence experiences

[rwunderlich]

See attached. I've loaded the data and then created a chart with ServiceId as Dimension and Expressions:

=only({<Stop={ASW}>} ActDepTime)

=only({<Stop={NIS}>} ActArrTime)

And then another column subtracting the two.

-Rob

[#4]

Rob thanks for the help.. I really am new to this, for some reason I cant open the QVW you sent but our IT is on to it..

In the meantime is there another way you could suggest to output the data in my original query in the format of the chart above with Journy time on the Y axis and the Departure time from ASW on the X axis.

[rwunderlich]

Are you using QV Personal Edition or a licensed version?

-Rob

[#6]

licenced edition... Ive sorted the access issue. just cant get it to look like the chart above

[rwunderlich · Accepted solution]

Hi Glenn,

The attached shows a chart that looks like yours. I've shown two ways to create  the dimension.

1. Calculated Dimension. This will work fine for a small amount of values.

2. New field "ASW Depart" created in the script. Recommended option.

If you need to transpose all the different stops into separate columns you can do that with GENERIC LOAD.

Qlikview Notes: Use cases for Generic Load

-Rob

http://masterssummit.com

http://robwunderlich.com

[#8]

Thanks Rob