Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Max of a calculated dimension in load script

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_DATEORIGIN_AIRPORT_IDDEP_DELAY_GROUP
1/1/201512478-1
2/1/201512478-1
3/1/201512478-1
4/1/201512478-1
5/1/201512478-1
1/1/201512892-1
2/1/201512892-1
3/1/2015128921
4/1/20151289210
5/1/201512892-1
26/1/201512892
27/1/201512892
28/1/2015128922
29/1/201512892-1
30/1/201512892-1
31/1/201512892-1
1/1/201512478-1
2/1/201512478-1
3/1/201512478-1
4/1/201512478-1
5/1/201512478-1
6/1/201512478-1
7/1/201512478-1
8/1/201512478-1
9/1/2015124780

Would really appreciate help!

1 Solution

Accepted Solutions

Re: Max of a calculated dimension in load script

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

11 Replies
t_chetirbok
Valued Contributor

Re: Max of a calculated dimension in load script

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

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?


talk is cheap, supply exceeds demand
balrajahlawat
Esteemed Contributor

Re: Max of a calculated dimension in load script

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)

Not applicable

Re: Max of a calculated dimension in load script

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

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

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

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

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

Per date or over all dates?


talk is cheap, supply exceeds demand
Not applicable

Re: Max of a calculated dimension in load script

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.

Community Browser