Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
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
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;
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?
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)
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%.
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
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)
The AIRPORT_ID in my example should be called ORIGIN_AIRPORT_ID in your code. Sorry about that.
All of the airports will have a certain proportion of flights that are delayed.
Per date or over all dates?
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.