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]

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.

[Peter_Cammaert · Accepted solution]

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