Common Practice Joining
Hi,
I am running into an issue where I am trying to join two datasets. Joining on a Unique ID is not an option as whoever set up this dataset decided not to include one. My next thought process was to join on date and name. Here is the issue I am running into. Dataset one has the full name for example Fredrick. Dataset 2 just has Fred. Its for multiple names in the datasets. I was thinking I could join them on last name instead but there are multiple last names that are similar. Is there a way to do index matching where Id just take the 3 first indexes and match them? What other ways would yall approach this?
thank you in Advance
Best Answer
-
You said it's for multiple names in the dataset - I'm interpreting that as not very many and you know which they are? I would suggest just cleaning up the names on the dataset in the ETL using the formula tile:
CASE WHEN `FirstName` = 'Fred' AND `LastName` = 'Johnson' THEN 'Frederick' ELSE `FirstName` ENDBecause even full names are not always unique, I suggest bringing in something like email or creating your own ID for each individual that is in your historic data and having a new ID generated for every name added later.
If I solved your problem, please select "yes" above
0
Answers
-
You said it's for multiple names in the dataset - I'm interpreting that as not very many and you know which they are? I would suggest just cleaning up the names on the dataset in the ETL using the formula tile:
CASE WHEN `FirstName` = 'Fred' AND `LastName` = 'Johnson' THEN 'Frederick' ELSE `FirstName` ENDBecause even full names are not always unique, I suggest bringing in something like email or creating your own ID for each individual that is in your historic data and having a new ID generated for every name added later.
If I solved your problem, please select "yes" above
0 -
I believe Its only for a few, however, I was trying to future proof it in case new names are added.
0 -
I'd work to find a better unique identifier than name for the future. The best place to do this is the source systems. Otherwise maintenance could be a headache.
If I solved your problem, please select "yes" above
0 -
Agreed, I didnt set up this dataset nor do I have access to go and change it at the source system.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive