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

[#1]

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.

ID	Date	Temp1	Temp2	Temp3	Temp4	Temp5	Temp6
3943337	04/01/2016	-0.32	0	0.49	0.84	1.93	2.67
3943337	04/01/2016	-0.32	0	0.49	0.84	1.93	2.67
3943337	04/01/2016	-0.32	0	0.49	0.84	1.93	2.67
3943337	04/01/2016	-0.32	0	0.49	0.84	1.93	2.67
3943338	04/01/2016	0.51	1.22	1.19	1.45	2.09	3.5
3943338	04/01/2016	0.51	1.22	1.19	1.45	2.09	3.5
3943338	04/01/2016	0.51	1.22	1.19	1.45	2.09	3.5
3943338	04/01/2016	0.51	1.22	1.19	1.45	2.09	3.5
3943342	04/01/2016	-1.34	-0.65	-0.3	0.15	0.7	1.21
3943342	04/01/2016	-1.34	-0.65	-0.3	0.15	0.7	1.21
3943342	04/01/2016	-1.34	-0.65	-0.3	0.15	0.7	1.21
3943342	04/01/2016	-1.34	-0.65	-0.3	0.15	0.7	1.21
3943339	05/01/2016	0.27	0.97	0.64	0.71	3.31	2.56
3943339	05/01/2016	0.27	0.97	0.64	0.71	3.31	2.56
3943339	05/01/2016	0.27	0.97	0.64	0.71	3.31	2.56
3943339	05/01/2016	0.27	0.97	0.64	0.71	3.31	2.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

ID	Date	Temp1	Temp2	Temp3	Temp4	Temp5	Temp6
3943337	04/01/2016	-0.32	0	0.49	0.84	1.93	2.67
3943338	04/01/2016	0.51	1.22	1.19	1.45	2.09	3.5
3943342	04/01/2016	-1.34	-0.65	-0.3	0.15	0.7	1.21
3943339	05/01/2016	0.27	0.97	0.64	0.71	3.31	2.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

[sunny_talwar · Accepted solution]

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;

[#3]

Thanks Sunny T!

Worked perfectly