Are Alteryx joins case sensitive?

Question ID: 109572
0
0

When using the join tool in Alteryx, are the fields used case-sensitive?  Do they have to be uppercase or lowercase?

Marked as spam
Posted by (Questions: 5, Answers: 1)
Asked on May 30, 2020 11:50 am
10 views
Answers
1
Private answer

Hey Frustrated!

Yes, as explained in this thread, joins in Alteryx are case sensitive everywhere, all of the time.

If you want to make sure that your joins work as often as possible, I'll offer five suggestions:

  1. Use a Formula tool or the Data Cleansing tool to make all text fields uppercase.
  2. If you know that you don't have any in your data, replace all spaces and special characters with underscores, and do not repeat them.  This ensures that "Atlanta, GA" joins with "Atlanta GA" -- both become "ATLANTA_GA" when using rules 1 and 2.
  3. If joining on numbers, never join on decimals (floats, doubles, and fixedDecimals).  Join on integers or turn them into strings.
  4. If time does not matter, change your DateTime fields to Date fields.  You have to do this before the Join tool; use a Select or Formula tool.
  5. If joining on a dimension, create a matching table in an Alteryx workflow (or just use Excel, if it's easier), and assign each value an ID with the UUIDCreate() function in the Formula tool (if the data is relatively small) or Record ID if speed is a concern.

If it would be a pain to pull your formatted text back out of these, simply create a new field, and join on that instead.

One last tip: if the values in your field may change over time, add a Test tool to the left and/or right outputs of your Join.  The test is really simple.  If all records are supposed to match, then enter 'Record Count Matches Specific Value' in Test Type and 0 for Test Value.  If it ever presents an error, then you know that data is new or has changed.

Joins in Alteryx can be difficult until you get the hang of them. Once you do, they're very valuable!

Good luck, and, if you have any further questions or just want to talk Analytics, please contact us at https://analytics.orasi.com/contact/.

Marked as spam
Posted by (Questions: 0, Answers: 4)
Answered on June 8, 2020 9:22 am