I have a table with multiple customers and their attributes. There are columns for each month that have the data for the category(Cost or Revenue). I also have a Fiscal Year column and a type column(Actual or Forecast).
My issue is how do I transform the data so I can sum for the whole year for each category and type? So for example I want to sum fiscal year 2015 Actual Revenue for Customer ABCD.
Attached is an example of what the table looks like. Any help is appreciated.
So, what you basically need to do is turn the individual fields you have (for the different months) into one field (for month_selection) and one (for the data), right?
That means some typework for you. You'll have to
- load everything first like you probably have already done;
- Do a nr. of RESIDENT LOADs, each time
- fixing (in a WHERE clause) the month to a certain value and
- concatenating all those RESIDENT LOADs
- At the end, you can delete the original file.
<=> Just be careful to keep that separate from the original file.
=> The result of this should be a table with a lot more records, but fewer columns
=> Voila. You have effectively transformed all those different fields with one month's data in each into a dimension which the user can select on.
