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

Mon, 01 Aug 2016 13:43:00 GMT

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

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

sunny_talwar — Mon, 01 Aug 2016 14:35:22 GMT

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;

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

Mon, 01 Aug 2016 15:00:07 GMT

Thanks Sunny T!

Worked perfectly

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

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

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

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