11 Replies Latest reply: Nov 25, 2015 2:27 PM by Andrew Walker RSS

    Finding the max value of a column

    Dingyong Huang

      Hi all

       

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

       

      I have a table:

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

       

      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!

        • Re: Finding the max value of a column
          Manish Kachhia
          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..

            • Re: Finding the max value of a column
              zubair khan

              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 Idcarrier id
              1a
              2a
              2b
              3b
              3c
              4a
              4c
              4

              d


              I hope this clears the ambiguity.

              • Re: Finding the max value of a column
                nagarjuna k

                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

                  • Re: Finding the max value of a column
                    Dingyong Huang

                    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

                  • Re: Finding the max value of a column
                    Dingyong Huang

                    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

                    • Re: Finding the max value of a column
                      Dingyong Huang

                      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!

                    • Re: Finding the max value of a column
                      Andrew Walker

                      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.

                        • Re: Finding the max value of a column
                          Dingyong Huang

                          Hi Andrew

                           

                          I tried your solution but there is an error. What's wrong?

                          Capture.PNG

                            • Re: Finding the max value of a column
                              Andrew Walker

                              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
                              11
                              22
                              32
                              43

                               

                              Then the max() function that is wrapped around the Aggr() function looks for the maximum value = 3.

                               

                              Hope this helps!

                                • Re: Finding the max value of a column
                                  Dingyong Huang

                                  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_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

                                   

                                  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

                                  [C:\Users\HUAN0_000\Desktop\Question.csv]

                                  (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;

                                    • Re: Finding the max value of a column
                                      Andrew Walker

                                      Hi Dingyong,

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

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

                                       

                                      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], ', ')
                                      11
                                      22, 3
                                      34

                                       

                                      It makes more sense when we add labels to the columns

                                       

                                      Number of Carriers Airport IDs
                                      11
                                      22, 3
                                      34

                                       

                                      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
                                      22, 3

                                       

                                      and the tablebox above becomes

                                       

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

                                       

                                      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