Join Question - Put a record on row with like data

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!

Join

Accepted answers

tcroberts

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!

All comments

tcroberts

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!

MarkA

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.

JoinedIWD_Data.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335