
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Finding the max value of a column
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!
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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..


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Dingyoung,
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.
Dear Manish
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 | d |
I hope this clears the ambiguity.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Manish Kachhia,
I want to know one thing
what is basic fundamental of using 0,1,-1 etc in expression.
Can u tell me thing?
Regards,
Nagarjuna

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Manish!!
Yeap, the max no. of carriers handled is 3 and is by airport 4. I was probably a typo on my part.
Dingyong

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
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
Dingyong


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dingyong,
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Manish
Is it possible to further create another variable from TotalCarrier / vMaxCarrier and then rank the airports in descending order based on this new variable?
Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Andrew
I tried your solution but there is an error. What's wrong?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dingyong,
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!

- « Previous Replies
-
- 1
- 2
- Next Replies »