Join returns zero results while excel Vlookup works

[saivig99]

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

[MarqueeCrew · Accepted solution]

Hi @saivig99,

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

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.

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

[saivig99]

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

[MarqueeCrew]

Glad to help clear that up.

Cheers,

Mark