Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am looking to do some calculations based on user productivity.
I have 2 tables joined using a link table which looks like the below:
Moves:
MoveKey | MoveCompleteTime | FetchUser | PickUser | PackUser |
1 | 2020-02-25 00:00 | CSMA | BAPA | GKDM |
2 | 2020-02-25 00:31 | CSMA | GOGA | FAPE |
3 | 2020-02-25 00:32 | HULI | NOPE | BACH |
4 | 2020-02-25 00:37 | HSAM | ROSA | BEAN |
User:
UserID | Name |
CSMA | Mark C |
BAPA | Pam B |
HULI | Liam H |
HSAM | Amar H |
GOGA | Gary G |
NOPE | Peter N |
ROSA | Sam R |
GKDM | Dmitry G |
FAPE | Peter F |
BACH | Chris B |
BEAN | Andy B |
The Code in my linked table is as follows:
OperatorMoveLink:
LOAD DISTINCT
[MoveKey],
[FetchUser] as [UserID],
'Fetch' as [Operator Role]
RESIDENT [Moves]
;
LOAD DISTINCT
[MoveKey],
[PickUser] as [UserID],
'Pick' as [Operator Role]
RESIDENT [Moves]
;
LOAD DISTINCT
[MoveKey],
[PackUser] as [UserID],
'Pack' as [Operator Role]
RESIDENT [Moves]
;
However, when i use a pivot table to show which Operators (by name) have handled a move, it shows every operator name that did 1 step of the move, against them all. So for MoveKey = 1 I see 9 operator names counted instead of 3.
I think this may be due to my use of the link table, is there another way?
Thanks!
looks like it's working fine for me
see attached