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 returns zero results while excel Vlookup works

saivig99
6 - Meteoroid

Hi Team , 

 

I am having trouble combining two altreyx .yxdb files , when I use the join tool it results in zero results. When I export the data out into an excel and try to match them using vlookup I am able to join the two data. 

 

Am I missing something.

 

Thanks

 

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @saivig99,

 

Excel vLookup doesn't always return the "BEST" results.  Here is an example:

 

Capture.PNG

 

MARK is found in both rows 1 & 2.  But it actually found "Mark" instead of "MARK".  If I use the little known parameter of FALSE (pictured below), then the answer is 6.

Capture.PNG

 

Alteryx will have 0 matches.  The join tool is CaSE SenSITivE.  If you have a "lookup" table (less than 32,000 records), you can use a FIND & REPLACE tool that isn't case sensitive.  That tool has the flexibility to partially match data and can either update an existing field (replace the value) or it can append fields from the lookup.

 

If you have a larger file or if you want to match to MANY records, I would suggest UPPERCASE([Field]) to both the lookup data and the incoming data and perform the join.  With the JOIN tool, you could have a match on say HOUSEHOLD and return all of the individuals that share that JOIN Key.

 

https://help.alteryx.com/11.0/index.htm#FindReplace.htm?Highlight=find replace

 

https://help.alteryx.com/11.0/index.htm#Join.htm?Highlight=join

 

Thanks,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
saivig99
6 - Meteoroid

Thanks Mark, the case sensitive fields were the issue in my case. 

MarqueeCrew
20 - Arcturus
20 - Arcturus

Glad to help clear that up.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels