Match and join data between 2 tables

[andymayo]

I have two  Tables A and B, both contain data on a person. Table A is the primary table with all the EMPLOYEE_NUMBERS in which is the correct id.  Table B has some correct id data in the ActionUser column in the case of 198937 there is a match if the Table A.ActionUser value is Trim() as all the values have a trailing space.

However the Employee '501045' has a value of 'RACZ01 ' in table B. So I need to map that value to a correct value of '501045' and then allow a join via the column name. I have tried applymap but I do not get a join.

TABLE A

EMPLOYEE_NUMBER, EMPNAME

'501045', 'Racz, Allan'

'198937', 'Smith John'

Table B

ActionUser, ActionName

'RACZ01 ', 'A,Racz'  // Trailing space on ActionUser String

'198937 ' ,'J.Smith'

This is what I have tried

MapEmpNum:
Mapping Load *
inline [
ActionUser,EMP_NUM
'RACZ01','501045'
];

TableA:

Load 

Employee_Number;

Select

Employee_Number

from Table A;

TableB:

Load

Trim(ActionUser) as ActionUser

FROM [lib://nnn/nnn.qvd] (qvd);

TableC:

Load Distinct

ApplyMap('MapEmpNum', If(IsText(Trim([ActionUser])), [ActionUser]), Trim([ActionUser])) AS EMPLOYEE_NUMBER

from TableB;

But I get all three values back and there is no link between Table A and Table B?