Join Question - Put a record on row with like data
- 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,
Sorry again I've been using Alteryx for 2 days now and am trying to join some data together.
I have joined together two data sources but would like the records from one of the data sources end up on the row from the first data source based on a value match. Here's the example:
When I join the data together the column "DEEP" and everything to the left is from source 1 and "Comp_Desc" and right is from source 2. I would like source 2 to join such that the record enters the joined table on the row where TopDepth is Nearest to Depth_WP. For example the first row from source 2 I would like to find the value in Depth_WP closest to 721.21 and have the record join on that row.
Any help available out there?
Thanks!
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Are the values of Depth_WP predictable, such as no values without outside of x.0 or x.5 as it appears to be the case from your screenshot? If so, you could create a new row with a Formula tool and round TopDepth to the nearest 0.5 (or 1) and try to join on that. If necessary you could also use the FLOOR() or CEIL() functions instead of just ROUND([TopDepth], 0.5)
How many Depth_WP values are there? If you can provide a list I can try to whip up an example workflow for you.
Cheers!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You are right that Depth_WP is predictable.. Mainly every 1 number. (that one decimal on row 7 is the only value with a decimal, and could even be removed). I have attached a spreadsheet of the data. There are around 1455 rows of Depth_WP.
I will try to see if I can use the formula tool as you suggest.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I tried as you said - I did get the data to join properly and new joined data has my TopDepth and Depth_WP on the same line, but it deletes all of the other records of the Depth_WP. Is there a way to keep those records when joining? Maybe I should be using the union tool?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, you'll want to union back in the records from the Depth_WP input to the J output.
Assuming your Depth_WP input is on the Left, the Top_Depth is on the right, you'll want to join the two as above, then union back in the LEFT UNJOIN anchor from your Join Tool.
If you want the Depth_WP to be in ascending order again, you'll need to add a sort tool after this union step. Note that everything from the Top_Depth input will be Null() for those records that did not join.
Glad I could help,
Cheers!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks again you're 2/2!