3 Replies Latest reply: Feb 12, 2018 11:13 AM by Digvijay Singh RSS

    How to use Group by?

    Jozsef Peitli

      Dear All,

       

      This is so basic but...

      How should I implement the Group by formula?

      Qlik Sense Enterprise (2017 Dec?)

       

      LOAD

          filefullpath,

          mid(filefullpath,39,2)&mid(filefullpath,42,2) as lot,

          mid(filefullpath,54,4) as iv,

          A,

          B,

          Delta_E as Delta_E_2iv,

          avg(Delta_E) as avgDelta_E,

          Delta_L as Delta_L_2iv,

          Delta_a as Delta_a_2iv,

          Delta_b as Delta_b_2iv,

          View_Id,

          Barcode

      FROM [lib://xml (pjrt_p_jozsi)/Lotok\Output_0212\*.csv] 

      (txt, codepage is 28591, embedded labels, delimiter is ';', msq) Group By A;

       

      Thank you all,

      Jozsef

        • Re: How to use Group by?
          Digvijay Singh

          There are some rules, when you are grouping by, all the fields for which  aggregate function is not used, needs to be included in group by. So you need to check if you are grouping things correctly and what all fields need to be included in the load when group by is used.

           

          For example as below -

          ClosedGrouping data

          Load fields grouped (aggregated) by ArtNo:

          LOAD ArtNo, round(Sum(TransAmount),0.05) as ArtNoTotal from table.csv group by ArtNo;

           

          Load fields grouped (aggregated) by Week and ArtNo:

          LOAD Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages from table.csv group by Week, ArtNo;

           

          More here -

          https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Load.htm

            • Re: How to use Group by?
              Digvijay Singh

              So in your load statement, you need to have all the fields except avgDelta_E, needs to be mentioned in the Group By clause. As of now you have just mentioned 'A'.

               

              LOAD

                  filefullpath,

                  mid(filefullpath,39,2)&mid(filefullpath,42,2) as lot,

                  mid(filefullpath,54,4) as iv,

                  A,

                  B,

                  Delta_E as Delta_E_2iv,

                  avg(Delta_E) as avgDelta_E,

                  Delta_L as Delta_L_2iv,

                  Delta_a as Delta_a_2iv,

                  Delta_b as Delta_b_2iv,

                  View_Id,

                  Barcode

              FROM [lib://xml (pjrt_p_jozsi)/Lotok\Output_0212\*.csv]

              (txt, codepage is 28591, embedded labels, delimiter is ';', msq) Group By A;

            • Re: How to use Group by?
              Youssef Belloum

              Hi,

               

              the rule is:

               

              all fields without aggregation function should be included in the Group by.

               

              so here you will have:

               

              .

              .

              .

              group by

                  filefullpath,

                  mid(filefullpath,39,2)&mid(filefullpath,42,2),

                  mid(filefullpath,54,4),

                  A,

                  B,

                  Delta_E,,

                  Delta_L ,

                  Delta_a ,

                  Delta_b,

                  View_Id,

                  Barcode;