This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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.
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 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.
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.
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!