Joining data and assuming nulls
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Everyone,
So I have 2 data sets that look like this:
City | EQPT | Month | Year | Completion Factor |
CIN | 787 | 6 | 2017 | 84.82% |
FIN | 737 | 6 | 2017 | 96.68% |
BIN | 747 | 6 | 2017 | 116.05% |
HOM | 717 | 6 | 2017 | 73.38% |
AUS | 737 | 6 | 2017 | 113.97% |
ATL | 787 | 6 | 2017 | 98.14% |
ATL | 787 | 6 | 2017 | 100.41% |
City | EQPT | Month | Year | Trips | Minutes | Hours | Miles |
CIN | 787 | 6 | 2018 | 753 | 82315 | 1371.918 | 451605 |
FIN | 737 | 6 | 2019 | 5 | 465 | 7.75 | 2513 |
BIN | 747 | 6 | 2017 | 156 | 14875 | 247.917 | 80558 |
HOM | 717 | 6 | 2018 | 133 | 15475 | 257.917 | 92117 |
AUS | 737 | 6 | 2019 | 137 | 14950 | 249.167 | 84109 |
ATL | 787 | 6 | 2019 | 704 | 78940 | 1315.666 | 433632 |
ATL | 787 | 6 | 2017 | 667 | 71035 | 1183.917 | 399953 |
What I am attempting to do is join these data sets to add the completion factor column for all rows that match by City, EQPT, Month, and Year. Then for all of the rows which receive a Null for completion factor (because it did not exist from data set 1), I want to use the previous year (same month and EQPT) completion factor. Additionally, if the EQPT for a previous year (same month) does not exist, how could I stipulate to use the completion factor for a different EQPT, but in the same Month/Year?
I hope this explained it well enough.
Solved! Go to Solution.
- Labels:
- Datasets
- Join
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You will need a few Join tools to achieve this, with each subsequent round using the unjoined records from the previous round. I've created the attached example solution to get you started. Also, I didn't know how you wanted to handle multiple matches so I removed the second ATL factor record to simplify the example.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you Charlie! I was able to make this work in the way that I needed. My end result ended up looking like this:
I was really struggling with trying to figure out outer joins, but your example made it click! Thank you again!