Max of a calculated dimension in load script

[#1]

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!

[t_chetirbok]

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;

[Gysbert_Wassenaar]

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?

[Anonymous]

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)

[#5]

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%.

[Peter_Cammaert]

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

[Peter_Cammaert]

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)

[Peter_Cammaert]

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

[Gysbert_Wassenaar]

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

Per date or over all dates?

[#10]

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.