Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have
year | month | value |
---|---|---|
2015 | 1 | 100 |
2015 | 2 | 600 |
2015 | 3 | 50 |
2016 | 2 | 300 |
2016 | 4 | 500 |
2016 | 8 | 800 |
2017 | 5 | 20 |
2017 | 7 | 40 |
2017 | 10 | 170 |
2017 | 12 | 600 |
And I want to calculate the share of each month in relation to the year.
In chart I can do it like this:
sum(total<month> value)/sum(total<year> value)
How can I do it in data loader?
Thanks.
Try this
Table:
LOAD * INLINE [
year, month, value
2015, 1, 100
2015, 2, 600
2015, 3, 50
2016, 2, 300
2016, 4, 500
2016, 8, 800
2017, 5, 20
2017, 7, 40
2017, 10, 170
2017, 12, 600
];
Left Join (Table)
LOAD year,
Sum(value) as YearlySum
Resident Table
Group By year;
Left Join (Table)
LOAD month,
Sum(value) as MonthlySum
Resident Table
Group By month;
Try this
Table:
LOAD * INLINE [
year, month, value
2015, 1, 100
2015, 2, 600
2015, 3, 50
2016, 2, 300
2016, 4, 500
2016, 8, 800
2017, 5, 20
2017, 7, 40
2017, 10, 170
2017, 12, 600
];
Left Join (Table)
LOAD year,
Sum(value) as YearlySum
Resident Table
Group By year;
Left Join (Table)
LOAD month,
Sum(value) as MonthlySum
Resident Table
Group By month;
Thank you very much!
It works with small amounts of data. But the script is not executed if there are many entries in the tables.
Try this
Table:
LOAD * INLINE [
year, month, value
2015, 1, 100
2015, 2, 600
2015, 3, 50
2016, 2, 300
2016, 4, 500
2016, 8, 800
2017, 5, 20
2017, 7, 40
2017, 10, 170
2017, 12, 600
];
Temp:
NoConcatenate
LOAD *
Resident Table
Order By year;
Left Join (Table)
LOAD year,
Sum(value) as YearlySum
Resident Temp
Group By year;
DROP Table Temp;
Temp:
NoConcatenate
LOAD *
Resident Table
Order By month;
Left Join (Table)
LOAD month,
Sum(value) as MonthlySum
Resident Temp
Group By month;
DROP Table Temp;
See if the above help you with the reload time....