Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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;

Not applicable
Author

Thanks Sunny T!

Worked perfectly