Deriving Transaction Data from Snapshot Data?

PhilipR — Sat, 16 Nov 2024 20:45:14 GMT

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

topic Deriving Transaction Data from Snapshot Data? in QlikView

Deriving Transaction Data from Snapshot Data?