Hi Team,
I have two dataset. In these dataset we have one common filed Employee in table1 and Loan officers in table2.
In Table-1 field Employee has the name and code
In Table-2 Loan officer has the name only.But in Table2 name filed has some anomalies.
as shown in pic: Table-1
. table2
we want to apply join with table1 and table2. Before applying the join we need to map the employee and loan officer.
ex. Devanand Madan-03224 present in Table-1 while Dev Madan in Table2. Both are same. Can we map these.
Attached the input files
Thanks!
For the Individual data table use this as a Join Calculation:
REGEXP_EXTRACT(REGEXP_EXTRACT([Employee],'(\s\w+[^-]\w+[^-])'),'\s*(\w+)\s*$')
//Modified to account for middle names, grabs last names only before the dash
It parses out the 2nd word (Last Name) of the Employee. Then for the Detailed data table use:
SPLIT([Loan Officers],' ',-1)
Which splits the Loan Officer last name. That should create the join between the two tables.
)//Modified to account for middle names, grabs last names only" style="display: block;" />
