Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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

1 Solution

Accepted Solutions

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

May be like this (Aggr in script is done using Group By statement):

Single_Value:

LOAD ID,

          Date,

          Avg(Temp1) as Single_Temp1,

          Avg(Temp2) as Single_Temp2,

          Avg(Temp3) as Single_Temp3,

          Avg(Temp4) as Single_Temp4,

          Avg(Temp5) as Single_Temp5,

          Avg(Temp6) as Single_Temp6

Resident CSV_Data

Group By ID, Date;

2 Replies

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

May be like this (Aggr in script is done using Group By statement):

Single_Value:

LOAD ID,

          Date,

          Avg(Temp1) as Single_Temp1,

          Avg(Temp2) as Single_Temp2,

          Avg(Temp3) as Single_Temp3,

          Avg(Temp4) as Single_Temp4,

          Avg(Temp5) as Single_Temp5,

          Avg(Temp6) as Single_Temp6

Resident CSV_Data

Group By ID, Date;

Highlighted
Not applicable

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

Thanks Sunny T!

Worked perfectly