Not matching common field for Join

[B_Y_ID8]

Hi Everyone,

Very new to Alteryx and I have searched the community - but not sure how I would go about it.

I have 2 tables- 1 Customer Account Table with CustomerID and 1 Organisation Table with OrganisationID. In a previous use case on SQL this has been joined as CustomerID = OrganisationID. How do I do this on Alteryx as it seems to only want matching fields when using the Join function.

Thanks for any help.

[joshuaburkhow]

Hey @B_Y_ID8 

Are you getting any errors? For Joins please make sure they are the same datatype. This should help you. The documentation for Alteryx is REALLY good so always remember to check it as it can give you everything you need foreach tool

https://help.alteryx.com/current/designer/join-tool

Hope this helps! If it does please mark as resolved so others can benefit! 

[atcodedog05]

Hi @B_Y_ID8 

Here is a interactive lesson on join 

https://community.alteryx.com/t5/Interactive-Lessons/Joining-Data/ta-p/76634

Right click on Join tool in your tool pallete. You will get an option example click on it. There you will find a workflow which explains the functionality

Hope this helps 🙂

[BrianR · Accepted solution]

In addition to what's been posted above, note that you can certainly join on fields that are named different, such as your example. However, Alteryx will attempt to join on values that match completely. As @joshuaburkhow noted, let's say CustomerID is a numeric datatype, and OrganisationID is a string datatype - this will result in an Error, the message will read String fields can only be joined to other string fields. The remedy for that would be a Select tool before your Join tool, and setting either CustomerID or OrganisationID to a string or a number (or vice-versa)...i.e., they should both match.

Now in terms of actual data - let's say in the CustomerID column, you have values such as this:

00100

00101

00102

And in the OrganisationID column, you have values such as this:

100

101

102

and both are String data types - When you join them, Alteryx will not yield any records out of the J anchor...because they would not match. The remedy for this would be to format either your OrganisationID, padding the left with zeroes, ensuring it is 5 characters wide to match with the Customer ID. There's a function for that, it's called PadLeft.

Hope this helps!

[B_Y_ID8]

Thanks for your help @BrianR and to everyone else.