topic Re: Max of a calculated dimension in load script in QlikView

Max of a calculated dimension in load script

Thu, 26 Nov 2015 00:00:04 GMT

What I need

Maximum proportion of delayed flights by airport ID. Ie., (max (delayed flights/ non cancelled & non delayed flights))

I want to create this dimension in the load script.

Data

The data file contains:

flight dates (FL_DATE)
origin of the flight (ORIGIN_AIRPORT_ID)
status of whether the flight was delayed or not (DEP_DELAY_GROUP) //more than 0 means its delayed. if there are no values, it means the flight is cancelled or diverted)

FL_DATE	ORIGIN_AIRPORT_ID	DEP_DELAY_GROUP
1/1/2015	12478	-1
2/1/2015	12478	-1
3/1/2015	12478	-1
4/1/2015	12478	-1
5/1/2015	12478	-1
1/1/2015	12892	-1
2/1/2015	12892	-1
3/1/2015	12892	1
4/1/2015	12892	10
5/1/2015	12892	-1
26/1/2015	12892
27/1/2015	12892
28/1/2015	12892	2
29/1/2015	12892	-1
30/1/2015	12892	-1
31/1/2015	12892	-1
1/1/2015	12478	-1
2/1/2015	12478	-1
3/1/2015	12478	-1
4/1/2015	12478	-1
5/1/2015	12478	-1
6/1/2015	12478	-1
7/1/2015	12478	-1
8/1/2015	12478	-1
9/1/2015	12478	0

Would really appreciate help!

Re: Max of a calculated dimension in load script

t_chetirbok — Thu, 26 Nov 2015 07:16:27 GMT

Hi!

If I understand, it's can be helpfull

tmp:

load ORIGIN_AIRPORT_ID, count(DEP_DELAY_GROUP) as DELAY

Resident TABLE_DATA

where DEP_DELAY_GROUP>0

group by ORIGIN_AIRPORT_ID;

left join

load ORIGIN_AIRPORT_ID, count(DEP_DELAY_GROUP) as ALL

Resident TABLE_DATA

where DEP_DELAY_GROUP <>'' // or isnull(DEP_DELAY_GROUP)

group by ORIGIN_AIRPORT_ID;

TABLE:

load ORIGIN_AIRPORT_ID, DELAY, ALL, DELAY /ALL as PROPORTION Resident tmp;

drop Table tmp;

Re: Max of a calculated dimension in load script

Gysbert_Wassenaar — Thu, 26 Nov 2015 07:31:00 GMT

I don't understand the question. There will only be one proportion per airport id. If you calculate a max from that you get only the airports with that maximum proportion. Or do you want the proportion per airport per date?

Re: Max of a calculated dimension in load script

Anonymous — Thu, 26 Nov 2015 07:39:21 GMT

At script create a flag like:

if(DEP_DELAY_GROUP='', 'Cancelled/Diverted',

if(DEP_DELAY_GROUP>0, 'Delayed', 'Not Delayed') as Air_Flag;

Then use this Flag to achieve what you are required like, If i got it:

=count({<Air_Flag={'Delayed'}>}FlightID)/count({<Air_Flag={'Not Delayed'}>}FlightID)

Re: Max of a calculated dimension in load script

Thu, 26 Nov 2015 08:09:40 GMT

All of the airports will have a certain proportion of flights that are delayed.

For example

Airport A: 10 flights, 2 flights delayed, 20% flights delayed

Airport B: 20 flights, 10 flights delayed, 50% flights delayed

Airport C: 20 flights, 5 flights delayed, 25% flights delayed

I need to determine the value 50%.

Re: Max of a calculated dimension in load script

Peter_Cammaert — Thu, 26 Nov 2015 08:36:03 GMT

Something like this (not so efficient but still single-step)?

Ratios:

LOAD AIRPORT_ID,

COUNT(IF (DEP_DELAY_GROUP > 0, 1)) / COUNT(IF (DEP_DELAY_GROUP <= 0, 1)) AS DEL_RATIO

RESIDENT RawData

WHERE IsNum(DEP_DELAY_GROUP)

GROUP BY AIRPORT_ID;

Best,

Peter

Re: Max of a calculated dimension in load script

Peter_Cammaert — Thu, 26 Nov 2015 08:38:08 GMT

Is it correct that you want the ratio of all delayed flights vs all non-delayed, non-cancelled, non-diverted flights instead of all flights that really took off (delayed or not)

Re: Max of a calculated dimension in load script

Peter_Cammaert — Thu, 26 Nov 2015 09:51:12 GMT

The AIRPORT_ID in my example should be called ORIGIN_AIRPORT_ID in your code. Sorry about that.

Re: Max of a calculated dimension in load script

Gysbert_Wassenaar — Thu, 26 Nov 2015 09:54:20 GMT

All of the airports will have a certain proportion of flights that are delayed.

Per date or over all dates?

Re: Max of a calculated dimension in load script

Thu, 26 Nov 2015 10:33:46 GMT

Ok. Further clarifications

For example

Airport A: 10 non cancelled flights (which is equivalent to 10 delayed and non delayed flights), 2 flights delayed, 20% flights delayed

All values are for over all dates

Thanks.

Re: Max of a calculated dimension in load script

t_chetirbok — Thu, 26 Nov 2015 10:54:52 GMT

In your exampleL Airport 12892 has 9 non cancelled flights and 3 delayed, that's mean 33 %. Is that right? if yes, see my first record.

Re: Max of a calculated dimension in load script

Peter_Cammaert — Thu, 26 Nov 2015 12:36:04 GMT

So this is different from your original requirement that said:

(max (delayed flights/ non cancelled & non delayed flights))

Now you want:

(max (delayed flights/ non cancelled & non delayed flights & delayed flights))

Correct?

If so, take the max() of the following table in a text box or another UI object:

Ratios:

LOAD ORIGINAL_AIRPORT_ID,

COUNT(IF (DEP_DELAY_GROUP > 0, 1)) / COUNT(DEP_DELAY_GROUP) AS DEL_RATIO

RESIDENT RawData

WHERE IsNum(DEP_DELAY_GROUP)

GROUP BY ORIGINAL_AIRPORT_ID;

or calculate the single max record by adding the following LOAD after the previous LOAD:

MaxRatio:

LOAD Max(DEL_RATIO) AS MAX_RATIO,

FirstSortedValue(ORIGIN_AIRPORT_ID, -DEL_RATIO) AS MAX_RATIO_AIRPORT_ID

RESIDENT Ratios;

See attachment (v2).

Best,

Peter