Airport: Load * Inline [ 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 ]; Carrier: Load [airport id], Count(DISTINCT [carrier id]) as TotalCarrier Resident Airport Group By [airport id]; MaxCarrier: Load Max(TotalCarrier) as MaxTotalCarrier Resident Carrier; Let vMaxCarrier = Peek('MaxTotalCarrier',0,'MaxCarrier'); Drop Table Carrier; Drop Table MaxCarrier;
The answer is 3 !
Not 4 which you have mentioned in your question..
If your requirement is to find max number of carrier_ids as a whole then the max number will be 4, but when you find max carrier_ids w.r.t airport_id then it is 3.
You are right, on the basis of given data, there are 3 carrier_ids against airport_id = 4 which is max number.
Airport Id carrier id 1 a 2 a 2 b 3 b 3 c 4 a 4 c 4
I hope this clears the ambiguity.
According to the manual:
peek( fieldname [, row [, tablename ]] ) returns the contents of the field fieldname in the record specified by
row in the input table tablename. Data is fetched from the internal QlikView database.
Fieldname must be given as a string (e.g. a quoted literal).
Row must be an integer. 0 denotes the first record, 1 the second record and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read. If no row is stated, -1 is assumed.
Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If the peek function is used outside the load statement or referring to another table, this parameter must be included.
Since I only have 1 row of data (the max value), using "0" returns that record.
Hope it helps
If you want to work this out create a variable such as:
vMaxNumcarrierByAirport = Max(Aggr(count(DISTINCT [carrier id]),[airport id]))
The Aggr function
Aggr(count(DISTINCT [carrier id]),[airport id])
creates a table in memory like a straight table. In this case the table has one dimension, [airport id], and the expression count(DISTINCT [carrier id]). Aggr function can take many dimensions but only one expression.
So we have an in-memory table giving the number of carriers for each airport and the max function wrapped around the Aggr looks for the maximum value of the expression and returns 3.
There is something I should have made clear before and I apologise for not doing so. The Aggr function is not used in the script, it is only used in objects in the user interface.
Create a text box and paste in the expression
= Max(Aggr(count(DISTINCT [carrier id]),[airport id]))
and you should get the answer 3.
When evaluating the Aggr part of the expression,
Aggr(count(DISTINCT [carrier id]),[airport id])
QlikView briefly creates in its memory something like this straight table
airport id Count(DISTINCT [carrier id]) 4 1 1 2 2 3 2 4 3
Then the max() function that is wrapped around the Aggr() function looks for the maximum value = 3.
Hope this helps!
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).
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?
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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
Group by ORIGIN_AIRPORT_ID;
Max (PROP_DEP_DELAY_FLIGHTS) as MAX_PROP_DEP_DELAY_FLIGHTS
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.