Hello,
I have a Excel file containing resource allocation data in Crosstable form. I was able to use normalize the data. How can I apply the employee name to all rows as a dimension?
Note: The starting month and the ending month changes every month i.e. forward looking (rolling 12 month data)
| Resource | Apr-16 | May-16 | Jun-16 | and so on |
|---|
| Smith, John | Primary Role: Developer | | | |
| Project 1 | 25% | 25% | 5% | |
| Project 2 | 10% | 10% | 20% | |
| Project 3 | 65% | 65% | 75% | |
| Grand Total | 100% | 100% | 100% | |
Using the crosstable command and additional date formatting/filtering, I am able to the table data below.
CrossTable(Month, Data)
LOAD * FROM
(biff, embedded labels, table is Sheet1$);
| Resource | Month (M/D/YYYY) | Data |
|---|
| Smith, John | 4/1/2016 | Primary Role: Developer |
| Project 1 | 4/1/2016 | 25% |
| Project 1 | 5/1/2016 | 25% |
| Project 2 | 4/1/2016 | 25% |
| Project 2 | 5/1/2016 | 25% |
| ....... | | |
How can I obtain the following resultant data set for all employees, all projects and all months?
Thanks!
Shaheer
| Name | Project | Period | Allocation |
|---|
| Smith, John | Project 1 | 4/1/2016 | 25% |
| Smith, John | Project 1 | 5/1/2016 | 25% |
| Smith, John | Project 1 | 6/1/2016 | 5% |
| Smith, John | Project 2 | 4/1/2016 | 10% |
| Smith, John | Project 2 | 5/1/2016 | 10% |
| ..... and so on | | | |
