Forum Discussion
Best Practice for equating multiple client names to a single client name
My data comes from different sources and in each source the names for my clients are different. So in my fact table name column I could have "Mr John Doe", "Mr J Doe" and "John Doe" but they all represent the same person.
Currently, I have a ClientNameMap table where ClientNameAlias is the primary key which looks as follows:
| Key | ClientName | ClientNameAlias |
| 1 | John Doe | Mr John Doe |
| 2 | John Doe | Mr J Doe |
| 3 | John Doe | John Doe |
Then I have an additional lookup table for ClientInfo about the client.
| Key | ClientName | City | Company |
| 1 | John Doe | London | Big Company |
| 2 | Jane Smith | Miami | Small Company |
Should I relate ClientInfo[ClientName] to ClientNameMap[ClientName] ?
Or should I relate ClientInfo[ClientName] to ClientNameMap[ClientNameAlias]?
In both cases I then relate ClientNameMap[ClientNameAlias] to Sales[ClientNameAlias]
Then I merge Sales with ClientNameMap on the ClientNameAlias column and expand ClientName so that if I want to display a table I can display the official ClientName and not all the different aliases.
Does this make sense? How should I be dealing with the different names for a single client?
I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.
1 Reply
I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.