Discussion Board for collaboration on QlikView Scripting.
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.
The data file contains:
Would really appreciate help!
Go to 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))
If so, take the max() of the following table in a text box or another UI object:
COUNT(IF (DEP_DELAY_GROUP > 0, 1)) / COUNT(DEP_DELAY_GROUP) AS DEL_RATIO
GROUP BY ORIGINAL_AIRPORT_ID;
or calculate the single max record by adding the following LOAD after the previous LOAD:
LOAD Max(DEL_RATIO) AS MAX_RATIO,
FirstSortedValue(ORIGIN_AIRPORT_ID, -DEL_RATIO) AS MAX_RATIO_AIRPORT_ID
See attachment (v2).
If I understand, it's can be helpfull
load ORIGIN_AIRPORT_ID, count(DEP_DELAY_GROUP) as DELAY
group by ORIGIN_AIRPORT_ID;
load ORIGIN_AIRPORT_ID, count(DEP_DELAY_GROUP) as ALL
where DEP_DELAY_GROUP <>'' // or isnull(DEP_DELAY_GROUP)
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>0, 'Delayed', 'Not Delayed') as Air_Flag;
Then use this Flag to achieve what you are required like, If i got it:
All of the airports will have a certain proportion of flights that are delayed.
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)?
COUNT(IF (DEP_DELAY_GROUP > 0, 1)) / COUNT(IF (DEP_DELAY_GROUP <= 0, 1)) AS DEL_RATIO
GROUP BY AIRPORT_ID;
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.
Per date or over all dates?
Ok. Further clarifications
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