Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
In load script i load table from excel:
Main:
LOAD
Id,
Year(Date)&num(Month(Date),'00') as YearMonth,
avg(Sum)
FROM
$(DataPath)Test.xlsx
(ooxml, embedded labels, table is Sheet1)
;
in result i get table:
Id | Date | Sum |
1 | 01.01.2015 | 4 |
1 | 02.01.2015 | 6 |
1 | 03.01.2015 | 4 |
1 | 04.01.2015 | 6 |
1 | 05.01.2015 | 37 |
1 | 06.01.2015 | 4 |
1 | 07.01.2015 | 2 |
1 | 08.01.2015 | 7 |
1 | 09.01.2015 | 4 |
1 | 01.02.2015 | 4 |
1 | 02.02.2015 | 6 |
1 | 03.02.2015 | 3 |
1 | 04.02.2015 | 6 |
1 | 05.02.2015 | 3 |
1 | 06.02.2015 | 1 |
1 | 07.02.2015 | 3 |
1 | 08.02.2015 | 5 |
1 | 09.02.2015 | 76 |
How i load script calculate average sum group by YearMonth?
My necessary result:
Id YearMonth Sum
1 201501 8.2
1 201502 11.8
Thanks.
Main:
LOAD
Avg(Sum) as AvgSum,
YearMonth
Group by
YearMonth;
LOAD
Id,
Year(Date)&num(Month(Date),'00') as YearMonth,
Sum
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1)
;
Main:
LOAD
Avg(Sum) as AvgSum,
YearMonth
Group by
YearMonth;
LOAD
Id,
Year(Date)&num(Month(Date),'00') as YearMonth,
Sum
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1)
;
Data:
Id,
Year(Date)&num(Month(Date),'00') as YearMonth,
Sum
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1);
Group:
noconcatenate
load Id,
YearMonth,
avg(Sum) as AvgSum
Resident Data
group by Id,YearMonth;
drop table Data;
Thanks!!
How i can add filed id Id to result table?
Hi,
Just set the Dimension to YearMonth and the expression to Avg(Sum) and format the number as Integer 1dp
And you'll get:
Just add ID as another dim if required.
HTH Andy
Thanks for advice.
But i need to do it in script level.
depends
if you have multiple ids per year-month (and I guess you do) then you can't
How do you want it to look?
Id YearMonth Sum
1 201501 8.2
1 201502 11.8
is id always 1?
if yes, just add min(id) as id in the first load
Main:
LOAD
min(Id) as Id,
Avg(Sum) as AvgSum,
YearMonth
Group by
YearMonth;
LOAD
Id,
Year(Date)&num(Month(Date),'00') as YearMonth,
Sum
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1)
;