Hi,
I'm working on a dashboard to track project progress. The data source I have only provides snapshot information (how many total hours were completed for a project,) which we could pull weekly--or daily if needed. I'd like to convert this to transnational records showing how many hours were worked on a project in a given week. I'v been playing with different ways to loop through the dataset, but I feel like I'm missing something obvious. Could any of you experts help?
My source data looks like:
| REPDATE | PID | PNAME | ESTHOURS | COMPHRS-BAL | REMHOURS-BAL |
| 5/6/2019 | 10 | Claims Dash | 40 | 5 | 35 |
| 5/6/2019 | 20 | Buy Model | 20 | 0 | 20 |
| 5/6/2019 | 30 | Loss Runner | 20 | 2 | 18 |
| 5/6/2019 | 40 | Premium Bdx | 50 | 3 | 47 |
| 5/13/2019 | 10 | Claims Dash | 40 | 9 | 31 |
| 5/13/2019 | 20 | Buy Model | 20 | 2 | 18 |
| 5/13/2019 | 25 | Sell Model | 20 | 5 | 15 |
| 5/13/2019 | 30 | Loss Runner | 20 | 4 | 16 |
| 5/13/2019 | 40 | Premium Bdx | 50 | 15 | 35 |
| 5/13/2019 | 50 | Excel Proj | 10 | 10 | 0 |
And my target table looks something like:
| REPDATE | PID | PNAME | COMPHRS-PERIOD |
| 5/6/2019 | 10 | Claims Dash | 5 |
| 5/6/2019 | 20 | Buy Model | 0 |
| 5/6/2019 | 30 | Loss Runner | 2 |
| 5/6/2019 | 40 | Premium Bdx | 3 |
| 5/13/2019 | 10 | Claims Dash | 4 |
| 5/13/2019 | 20 | Buy Model | 2 |
| 5/13/2019 | 25 | Sell Model | 5 |
| 5/13/2019 | 30 | Loss Runner | 2 |
| 5/13/2019 | 40 | Premium Bdx | 12 |
| 5/13/2019 | 50 | Excel Proj | 10 |
Assuming we will be adding projects along the way.
What's the best approach?
Phil
