Finding the max value of a column

[#1]

Hi all

I am new to qlikview and would really appreciate help on the following:

I have a table:

airport id	carrier id
1	a
2	b
3	c
4	a
1	a
2	b
3	b
4	c
1	a
2	a
3	b
4	d

Here is my script:

Airport:

LOAD [airport id],

     [carrier id]

FROM

C:\Users\1571091F\Desktop\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Distinct_carriers_at_airport:

LOAD [airport id],

Count(DISTINCT [carrier id]) as [No. of distinct carriers]

Resident Airport

Group by [airport id];

After here, I would like to extract the max [No. of distinct carriers] and assign it to a variable. How can i do it? The value that should be extracted should be 4 (from airport_id 4).

Thanks!

[#2]

Thanks for clarifying. Unfortunately, i require it as a dimension for further processing.

I have another question and if you could help me, I would be eternally grateful to you (really desperate here).

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

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

What I tried

I tried to create a dimension that calculates the maximum proportion of delayed flights and group it by airport. I used the following script but it doesn't work. Why? How can I resolve it?

OTP:

LOAD FL_DATE,

     ORIGIN_AIRPORT_ID,

     DEP_DELAY_GROUP

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

PROP_DEP_DELAY_FLIGHTS:

LOAD

ORIGIN_AIRPORT_ID,

if(DEP_DELAY_GROUP>0, count (FL_DATE), 'NULL')/count (FL_DATE) as PROP_DEP_DELAY_FLIGHTS//if the dep_delay_delay group is more than zero, its a delayed flight

Resident OTP

Group by ORIGIN_AIRPORT_ID;

MAXPROP_DEP_DELAY_FLIGHTS:

LOAD

Max (PROP_DEP_DELAY_FLIGHTS) as MAX_PROP_DEP_DELAY_FLIGHTS

Resident PROP_DEP_DELAY_FLIGHTS;

[effinty2112]

Hi Dingyong,

We can use the Aggr function to make a calculated dimension. Taking your first set of data

airport id	carrier id
1	a
2	a
2	b
3	b
3	c
4	a
4	c
4	d

This straight table with a calculated dimension groups the airport ids by the number of distinct carriers.

=Aggr(count(DISTINCT [carrier id]),[airport id])	Concat(Distinct [airport id], ', ')
1	1
2	2, 3
3	4

It makes more sense when we add labels to the columns

Number of Carriers	Airport IDs
1	1
2	2, 3
3	4

You can select on the calculated dimension in the straight table as if it was a dimension straight from the data model. Click on 2

Number of Carriers	Airport IDs
2	2, 3

and the tablebox above becomes

airport id	carrier id
2	a
2	b
3	b
3	c

Aggr is great but it takes a bit of time to master but it's well worth it.

I don't know what I can demonstrate with your second data set. Is this derived from a larger data set of individual flights? If so maybe I can do something with that. Hopefully.

Regards