community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Join Question - Put a record on row with like data

Meteoroid

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:

 

2018-09-07_9-53-35.png

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!

Alteryx Partner

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! 

Meteoroid

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.

 

Meteoroid

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?

Alteryx Partner

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!

Meteoroid

Thanks again you're 2/2!

Labels