Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Many to Many Join Not Working in Alteryx

Highlighted
6 - Meteoroid

Hi there,

 

I am doing a simple workflow and I cannot seem to get the Many to Many Join to work correctly in Alteryx.  I am linking two Excel spreadsheets on Medical Record Number, Claim ID, and Amount Paid (all numeric characters, no alphas).

 

I have about 30,000 records in each Excel file, (duplicates in both files), and the correct output should be 1,317 records (ran this same test in ACL).  When I do it in Alteryx, I only get 155 records.

 

I am actually doing a trial of Alteryx and to be honest, I am not too impressed.  I do a fair amount of complicated data analytics using ACL and IDEA, and I am very surprised that Alteryx is struggling with what I consider to be a very basic test - input two simple Excel files and then join them on 3 fields.  

 

What am I missing?  Thanks in advance for your help!

 

Mike

Highlighted
17 - Castor
17 - Castor

Hi  @mkeiffer 

 

Without any more information than what you've provided, it's difficult to come up with an answer. Is there anyway that you could anonymize the client IDs and post a minimal data set that demonstrates the issue you're having.  This would go a long way to allowing us to help.   

 

Dan

Highlighted
Alteryx Certified Partner

Hi @mkeiffer

 

Glad to hear you're giving Alteryx a trial but disappointed to hear you're having issues. It's hard to diagnose the issue you're seeing without looking at your data. I know that you're probably dealing with PHI, so you can't post it but is there any way you can give us a small sample that is anonymous so we can help you out?

 

Thanks!

Highlighted
6 - Meteoroid

Thanks for everyone who responded.  Looks like the issue was a learning curve on my part.  I went back and looked at the views of my data and it looks like trailing blank spaces were causing the issue.  I went back and I used the Cleanse tool on my fields prior to running the Join and it looks like that fixed the issue.

 

Thanks so much everyone who responded so quickly!  

 

Highlighted
9 - Comet

Hi @mkeiffer 

 

As others have pointed out, its hard to determine the cause without seeing some of your data. But there is one thing that could be causing the issue based off of what you're using as matches. If you are using Amount Paid as field for Joining, that could be causing you issues. Since those would be dollar amounts with decimals, Alteryx will read them in as doubles. Joins on Double or Float are not recommended in Alteryx due to rounding errors. You could try to change those to fixed decimals to see if it corrects the error. 

 

Thanks!

Highlighted
6 - Meteoroid

That is one of the first things that I did, changing the numeric fields to fixed decimal.  Looks like the issue was trailing spaces on some of the data in the right file I was trying to join.  I used the Data Cleansing tool prior to running the Join and that fixed the issue.  Thanks so much for responding!

Highlighted
Alteryx Certified Partner

@mkeiffer 

 

That is great news! Good luck on your Alteryx learning journey!

 

 

 

 

 

Labels