Discussion Board for collaboration related to QlikView App Development.
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
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;
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;
Thanks Sunny T!
Worked perfectly