Several references to same dimension table, avoiding circular references

[FernandaNava]

Say I have a fact table with several fields, among which there are columns that represent actions done by employees at different stages of the process.

Transaction	Date	Type	ReceivedBy	ProcessedBy	DeliveredBy
1	15/07/2020	A	58	78	63
2	16/07/2020	A	63	24	22
3	17/07/2020	C	58	27	21
4	14/08/2020	B	21	26	58
5	12/07/2020	D	24	21	79
6	18/09/2020	B	78	63	41
7	19/09/2020	D	63	78	26

These are all the same employees contained in a table:

Employees	Name	LastName
21	Juan	Diaz
22	Luis	Smith
24	John	Rodriguez
26	Robert	Lopez
27	Sam	Black
41	Peter	White
58	Tony	Rivera
63	Anna	Johnson
78	Mary	Diaz
79	Lucy	Carter

How to reference the table?  I tried doing:

Employees	Name	LastName	ReceiverID	ProcesserID
21	Juan	Diaz	21	21
22	Luis	Smith	22	22
24	John	Rodriguez	24	24

Transaction	ReceiverID
1	58
2	63
3	58

Transaction	ProcesserID
1	78
2	24
3	27

But this created circular referencing issues. Any suggestions would be very appreciated. 

[Taoufiq_Zarra · Accepted solution]

@FernandaNava  if I understood correctly, you need to add employee information to Fact table ?

Mayebe like this :

MappingName:

mapping load Employees, Name&'_'&LastName as Nametmp INLINE [
    Employees, Name, LastName
    21, Juan, Diaz
    22, Luis, Smith
    24, John, Rodriguez
    26, Robert, Lopez
    27, Sam, Black
    41, Peter, White
    58, Tony, Rivera
    63, Anna, Johnson
    78, Mary, Diaz
    79, Lucy, Carter
];



Fact:
LOAD Transaction,
 Date,
 Type,
 ReceivedBy,
 subfield(applymap('MappingName',ProcessedBy),'_',1) as NameProcessEmployee ,
  subfield(applymap('MappingName',ProcessedBy),'_',2) as LastNameProcessEmployee ,
   subfield(applymap('MappingName',DeliveredBy),'_',1) as NameDelivereEmployee ,
  subfield(applymap('MappingName',DeliveredBy),'_',2) as LastNameDelivereEmployee


INLINE [
    Transaction, Date, Type, ReceivedBy, ProcessedBy, DeliveredBy
    1, 15/07/2020, A, 58, 78, 63
    2, 16/07/2020, A, 63, 24, 22
    3, 17/07/2020, C, 58, 27, 21
    4, 14/08/2020, B, 21, 26, 58
    5, 12/07/2020, D, 24, 21, 79
    6, 18/09/2020, B, 78, 63, 41
    7, 19/09/2020, D, 63, 78, 26
];

output:

[FernandaNava]

Yes I think this would work! Just a quick question: do I have to load an Employees table before the "Mapping Name" one, or would this be the first time I'm loading this data? What if  the Employees table had more columns such as "type" and "username", can I just load them "normally"?

Also, I assume you did the subfield part to keep Name and Last Name separated, but I'm guessing I could just have one field with the full name and skip this part?

Thank you!

[Taoufiq_Zarra · Accepted solution]

@FernandaNava  No the first time you load EMployee table is in the mapping section (One time in Mapping section)

to add other information for me i prefer using mapping and subfield like for example if you want add type and username, you can do :

MappingName:

mapping load Employees, Name&' '&LastName&'|'&type&'|'&username as EmplyeeName INLINE [
    Employees, Name, LastName,type,username
    21, Juan, Diaz,a,z
    22, Luis, Smith,z,e
    24, John, Rodriguez,r,r
    26, Robert, Lopez,t,e
    27, Sam, Black,t,c
    41, Peter, White,y,u
    58, Tony, Rivera,d,d
    63, Anna, Johnson,g,h
    78, Mary, Diaz,az,rfef
    79, Lucy, Carter,lkj,rr
];



Fact:
LOAD Transaction,
 Date,
 Type,
 ReceivedBy,
 subfield(applymap('MappingName',ProcessedBy),'|',1) as NameProcessEmployee ,
  subfield(applymap('MappingName',DeliveredBy),'|',1) as NameDeliveredByEmployee ,
    subfield(applymap('MappingName',ProcessedBy),'|',2) as typeProcessEmployee,
subfield(applymap('MappingName',ProcessedBy),'|',3) as usernameProcessedByEmployee,
  subfield(applymap('MappingName',DeliveredBy),'|',2) as typeDeliveredByEmployee,
subfield(applymap('MappingName',DeliveredBy),'|',3) as usernameDeliveredByEmployee

INLINE [
    Transaction, Date, Type, ReceivedBy, ProcessedBy, DeliveredBy
    1, 15/07/2020, A, 58, 78, 63
    2, 16/07/2020, A, 63, 24, 22
    3, 17/07/2020, C, 58, 27, 21
    4, 14/08/2020, B, 21, 26, 58
    5, 12/07/2020, D, 24, 21, 79
    6, 18/09/2020, B, 78, 63, 41
    7, 19/09/2020, D, 63, 78, 26
];

output :

[FernandaNava]

Ok, I understand!  It's like a more agile way of doing a join.

Thank you!

[Taoufiq_Zarra]

yes@FernandaNava  "don't join-use Applymap instead"  as HIC said