2 Replies Latest reply: Aug 1, 2016 11:00 AM by Steve Wilson RSS

    Using Aggr() in the data load editor (QLIKSENSE Set Let As)

    Steve Wilson

      Hi Everyone ,

       

      The data below is a sample of a larger SQL table and as you can see each ID is repeated four times. In this example all the temperature values associated with a specific ID are identical, however this is not always the case.

       

      Sample Table.

      IDDateTemp1Temp2Temp3Temp4Temp5Temp6
      394333704/01/2016-0.3200.490.841.932.67
      394333704/01/2016-0.3200.490.841.932.67
      394333704/01/2016-0.3200.490.841.932.67
      394333704/01/2016-0.3200.490.841.932.67
      394333804/01/20160.511.221.191.452.093.5
      394333804/01/20160.511.221.191.452.093.5
      394333804/01/20160.511.221.191.452.093.5
      394333804/01/20160.511.221.191.452.093.5
      394334204/01/2016-1.34-0.65-0.30.150.71.21
      394334204/01/2016-1.34-0.65-0.30.150.71.21
      394334204/01/2016-1.34-0.65-0.30.150.71.21
      394334204/01/2016-1.34-0.65-0.30.150.71.21
      394333905/01/20160.270.970.640.713.312.56
      394333905/01/20160.270.970.640.713.312.56
      394333905/01/20160.270.970.640.713.312.56
      394333905/01/20160.270.970.640.713.312.56

      Using the data load editor my aim is to create a separate table which contains the IDs and their respective average temperature values. (see the table below)

       

      For Example

       

      IDDateTemp1Temp2Temp3Temp4Temp5Temp6
      394333704/01/2016-0.3200.490.841.932.67
      394333804/01/20160.511.221.191.452.093.5
      394334204/01/2016-1.34-0.65-0.30.150.71.21
      394333905/01/20160.270.970.640.713.312.56

      I tried doing this a few ways using a combination of AGGR() and AVG(), but every attempt ended in an error . To keep things simple I just used Temp1.

       

      Attempt 1

      Single_Value:

      Load

            Temp1,

            aggr(Avg(Temp1),ID) as Single_Temp1

      Resident CSV_Data;

       

      Output --> Error in expression: AGGR is not a valid function :CSV_Data

       

      Attempt 2

      Single_Value:

      Load

          Temp1,

          set Single_Temp1 = AGGR(avg(Temp1),ID)

      Resident CSV_Data;

       

      Output -->  Syntax error, missing/misplaced FROM:

       

      Attempt 3

      Single_Value:

      Load

          Temp1,

          let '=Single_Temp1 = AGGR(avg(Temp1),ID)'

      Resident CSV_Data;

        

      Output --> Syntax error, missing/misplaced FROM:

       

      If anyone could shed some light on why I am getting these errors that would be amazing.

       

      Thanks

      Steve