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!
