7 Replies Latest reply: Feb 16, 2018 12:23 PM by Petter Skjolden RSS

    Group the numbers

    Sachin Nataraj

      Hello,

       

      How do i group the numbers in Qlik sense to create a table & a graph.

       

      I have different numbers but i want to group these numbers in one order.

      weight.PNG

      I need to group these numbers in first column like this

      result.PNG

      What formula should i use and how can i get it in a table like above and also in a bar graph or line graph?

       

      Thank you in advance

       

      Sachin

        • Re: Group the numbers
          Youssef Belloum

          Hi,

           

          you have two methods to Group your data:

           

          1. on the script with a Group by

           

          2. on the front-end with aggregation functions like Sum, Count, Aggr......

           

          it depends on what you need

            • Re: Group the numbers
              Sachin Nataraj

              Hello Youssef,

               

              What if i use the front-end

               

              can you give an example

               

              columnA      ColumnB

              45                    1

              30                    2    

              100                   5

              75                    1

              12                    13    

              5                         1

              25                    18

               

              i need something like to be grouped in Qlik sense like this

               

              columnA          columnB

              0-25                    32

              25-50                    2

              50-75                    1

              75-100                   5

               

              How can i get this

                • Re: Group the numbers
                  Youssef Belloum

                  you can use this as a dimension:

                   

                  =if(columnA>=0 and columnA<=25,'0-25',

                  if(columnA<=50 and columnA>=25,'25-50',

                  if(columnA<=75 and columnA>=50,'50-75',

                  if(columnA<=100 and columnA>=75,'75-100'))))


                  you can use Class() but you will be limited on the result

              • Re: Group the numbers
                Josefina Fasoli

                For grouping you can use the class function.

                 

                Class Function Example

                • Re: Group the numbers
                  Petter Skjolden

                  Here is a way to do it if you need non-uniform bins/bucket sizes. The IntervalMatch() function can be used along with a interval table that specify the different binsizes:

                   

                  CARGO:
                  LOAD * INLINE [
                  ID, Weight,Neg,Op,Pos
                  1, 30,1 
                  2, 30,,,1
                  20, 99,1
                  3, 100,1
                  4, 50,1,,1
                  5, 490,,1
                  6, 300,1
                  7, 299,,1
                  8, 101,1
                  9, 200,,1,
                  ];
                  
                  
                  GROUPS:
                  LOAD * INLINE [
                  From,To,WeightGroup
                  0,100,100 KG
                  101,200,200 KG 
                  201,300,300 KG
                  301,499,500 KG
                  ];
                  
                  
                  INNER JOIN
                    IntervalMatch( Weight )
                    LOAD 
                      From, To
                    RESIDENT 
                      GROUPS;
                  
                  
                  LEFT JOIN (GROUPS) LOAD * RESIDENT CARGO;
                  DROP TABLE CARGO;
                  RENAME TABLE GROUPS TO CARGO;
                  
                  
                  // DELETE THE NEXT LINES IF YOU NEED THE CARGO TABLE AND NOT JUST THE SUMMARY 
                  GROUPED:
                  LOAD 
                   WeightGroup,
                      Sum(Neg) AS Negative,
                      Sum(Neg)/Count(WeightGroup) AS NegRatio,
                      Sum(Op) AS Open,
                      Sum(Op)/Count(WeightGroup) AS OpenRatio,
                      Sum(Pos) AS Positive,
                      Sum(Pos)/Count(WeightGroup) AS PosRatio,
                      Count(WeightGroup) AS Total 
                  RESIDENT 
                   CARGO 
                  GROUP BY 
                   WeightGroup;
                  DROP TABLE CARGO;