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?
