Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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;

View solution in original post

Not applicable

Thanks Sunny T!

Worked perfectly