1 Reply Latest reply: Nov 29, 2015 1:59 PM by Massimo Grossi RSS

    Using group by on multiple columns in Qlik Sense Load Script

      I have to find the Max Effectivity Year per PIN(PCINUM,MDINum,BINum,SINum,Parcel,PType). Then join the result with my table on PINPIN(PCINUM,MDINum,BINum,SINum,Parcel,PType) and Effectivity Year to get the result.

      LOAD

      PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType,

          OwnerNum,

          yr;

      SQL SELECT PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType,

          OwnerNum,

          yr from PROP.PUB.Property INNER JOIN

      (SQL SELECT PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType,

          max(yr) as max_yr from PROP.PUB.Property WHERE yr <= 2015 GROUP BY SQL SELECT PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType) Property2 ON Property.PCINum,

          Property.MDINum,

          Property.BINum,

          Property.SINum,

          Property.Parcel,

          Property.PType = Property2.PCINum,

          Property2.MDINum,

          Property2.BINum,

          Property2.SINum,

          Property2.Parcel,

          Property2.PType AND Property.yr = Property2.max_yr;

       

      Is this possible in qlik sense?

        • Re: Using group by on multiple columns in Qlik Sense Load Script
          Massimo Grossi

          an example, if I understand the question

           

          TABLE:

          load

              Dim1, Dim2, Dim3,                         // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)

              Dim4, Dim5,

              [Effectivity Year],

              Exp1, Exp2;

          sql select .......;

           

          left join (TABLE)

          load

              Dim1, Dim2, Dim3,                         // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)

              max([Effectivity Year]) as MaxYear

          resident

              TABLE

          group by

              Dim1, Dim2, Dim3;                         // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)