topic Re: Re: Run Times in QlikView

Run Times

Tue, 07 Jan 2014 16:57:16 GMT

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

Re: Run Times

jvitantonio — Tue, 07 Jan 2014 17:56:28 GMT

Hi,

Please find the solution in the attached app.

I hope it helps.

BI Experience | A place to share our Business Intelligence experiences

Re: Run Times

rwunderlich — Tue, 07 Jan 2014 18:02:45 GMT

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

Re: Run Times

Wed, 08 Jan 2014 17:23:59 GMT

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.

Re: Run Times

rwunderlich — Wed, 08 Jan 2014 18:28:24 GMT

Are you using QV Personal Edition or a licensed version?

-Rob

Re: Run Times

Fri, 10 Jan 2014 14:52:01 GMT

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

Re: Re: Run Times

rwunderlich — Fri, 10 Jan 2014 16:09:53 GMT

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

Re: Re: Run Times

Thu, 16 Jan 2014 15:06:04 GMT

Thanks Rob