I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.
The data is all employee data, the problem is, I only have one file of seven with a unique
Employee ID, and the format of
Employee_Name is inconsistent across about half the files, so I can't join on
Employee_Name (capitalization, commas, and initials are all inconsistent). Do I need to write macros to create consistency in
Employee_Name so I can write the lookups to create the proper primary/foreign key relationships, or is there a more succinct way to do this?
My first choice is to go back to the client, and ask for new data exports with a common
Employee_ID field across all of them, but I don't know if or when that's feasible.
I should also note, I already wrote a bunch of code in R to solve this problem and perform the joins, but the client needs a solution that doesn't rely on me running an R script every time they want to update their data.
I'm also all ears if there's a better solution with SQLite or something like that.