Weekly Capacity Table

[chrismtb]

I have a set of employee records in a source table with ID, Name, Start Date, Weekly Capacity and Team.

e.g.

ID	Name	Start Date	Weekly Capacity (hrs)	Team
1	Steve	01/01/20	35	Team A
2	Mike	06/01/20	35	Team B
3	Chris	13/01/20	40	Team C
4	Dave	06/01/20	35	Team A
5	Mark	01/03/20	40	Team B

I am able to create a fairly simple script or table that aggregates the weekly capacity by team so from the above Team A = 70 hours.

This doesn't take into account start date. What I really want to do (via script or table expression) is to produce a a weekly view of capacity by team.

Desired outcome would be:

Week No	Team	Weekly Capacity (hrs)
1	Team A	35
1	Team B	0
1	Team C	0
2	Team A	70
2	Team B	35
2	Team C	0
3	Team A	70
3	Team B	35
3	Team C	40

Can anyone help me with the approach i should be taking for this?

Thanks

[MayilVahanan]

HI @chrismtb 

Try like this

T1:
LOAD * INLINE [
ID, Name, Start Date, Weekly Capacity (hrs), Team
1, Steve, 01/01/20, 35, Team A
2, Mike, 06/01/20, 35, Team B
3, Chris, 13/01/20, 40, Team C
4, Dave, 06/01/20, 35, Team A
];

T2:
NoConcatenate
LOAD *,
If(Team <> Peek(Team),[Weekly Capacity (hrs)], [Weekly Capacity (hrs)]+Peek([Weekly Capacity (hrs)])) as WeeklyCapacityTemp Resident T1 Order by Team, [Start Date];

Temp:
LOAD Distinct Team Resident T1;
Join
LOAD Distinct [Start Date] Resident T1;

Join(T2)
Load *, Week([Start Date]) as Week Resident Temp;

Final:
NoConcatenate
LOAD *, If(WeeklyCapacityTemp = 0 or IsNull(WeeklyCapacityTemp), Peek(WeeklyCapacityTemp), WeeklyCapacityTemp) as WeeklyCapacity Resident T2 Order by Team, Week ;
DROP Table Temp, T1, T2;

O/p:

[chrismtb]

@MayilVahanan 

Thanks for the suggestion - this seems to go some way to answering my question, however I have several weeks that are blank across the year - where no new employees start.

when I use the above code with my data sets I get a number of blanks across weeks where I would expect to see the sum(WeeklyCapacity) number maintained across these weeks.

so with my example input changed to

T1:
LOAD * INLINE [
ID, Name, Start Date, Weekly Capacity (hrs), Team
1, Steve, 01/01/20, 35, Team A
2, Mike, 06/01/20, 35, Team B
3, Chris, 13/01/20, 40, Team C
4, Dave, 06/01/20, 35, Team A
5, Pete, 21/01/20, 15, Team C
6, Mark, 27/01/20, 35, Team A
]

I now get the following O/p:

 

but you can't really see the issue until you change the shape of the table to this:

The result I am expecting / need for Team A in week 4 is 70 and for week 5 is 105

Similarly with Team B, week 1 is showing 70 when it should be blank and  both week 4 and week 5 should be showing 35 as the weekly capacity is enduring across time.

Chris.

[MayilVahanan]

Hi @chrismtb 

Try like below

T1:
LOAD * INLINE [
ID, Name, Start Date, Weekly Capacity (hrs), Team
1, Steve, 01/01/20, 35, Team A
2, Mike, 06/01/20, 35, Team B
3, Chris, 13/01/20, 40, Team C
4, Dave, 06/01/20, 35, Team A
5, Pete, 21/01/20, 15, Team C
6, Mark, 27/01/20, 35, Team A
];

T2:
NoConcatenate
LOAD *,
If(Team <> Peek(Team),[Weekly Capacity (hrs)], [Weekly Capacity (hrs)]+Peek(WeeklyCapacityTemp)) as WeeklyCapacityTemp Resident T1 Order by Team, [Start Date];

Temp:
LOAD Distinct Team Resident T1;
Join
LOAD Distinct [Start Date] Resident T1;

Join(T2)
Load *, Week([Start Date]) as Week Resident Temp;

Final:
NoConcatenate
LOAD *, If(WeeklyCapacityTemp = 0 or IsNull(WeeklyCapacityTemp) and Team = Peek('Team'), Peek(WeeklyCapacity), WeeklyCapacityTemp) as WeeklyCapacity Resident T2 Order by Team, Week ;

DROP Table Temp, T1, T2;

outpu: