Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Join function : VLOOKUP works, but not joining fields

MO21
6 - Meteoroid

Hi everybody

 

I have workflow that has to join based on two matching columns.

I included output where VLOOKUP function finds match however Alteryx is not finding the match.

 

I have however tried all the trim, REGEX_replace etc functions and still no luck. 

So if there is additional solution please comment!!

 

Will appreciate any help 🙂

4 REPLIES 4
AlteryxUserFL
11 - Bolide

You data has some hidden character that Alteryx is struggling with. Notepad also can't identify the characters. I'm checking to see if there is anything you can do inside Alteryx to fix this, but you may need to clean the data before sending it into Alteryx (Save Excel data to Notepad --> Close Notepad --> Open Notepad --> Remove "?" --> Paste into a new Excel Sheet --> Alteryx)

 

Example 1.PNGExample 2.PNG

afv2688
16 - Nebula
16 - Nebula

Hello @MO21,

 

Copy the cells that should match and paste them on a text input tool. Then use the formula and try to see if they are equal [Field1] = [Field2]

 

If you detect the special character you could use a regex_replace([Field1], 'character', '') to try to delete it. (worked for me in the past)

 

If not, there must be some special character that you are not taking into account. Remember that joins are case sensitive.

 

Regards

afv2688
16 - Nebula
16 - Nebula

Hello @MO21,

 

It seems your problem is the match ID local column.

 

use this formula to modify the field:

 

REGEX_Replace([F3], '[^\d|\-]', '$1')

 

And you should be good to go. It takes out every character that is not either a number or a "-"

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

MO21
6 - Meteoroid

Works perfect!  Thank you so much!

 

Spent quite some time trying all format modifications etc and exporting the data but no luck.

This one works 100% for matching the records.

Labels