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 re-opening this case since it has been left unresolved. Just a few background, there are two input files. First file has no work date where I use a formula to add a column date to locate a match date in the 2nd file. The 2nd file has more records because it contains month to date records. The quantify in the First file is sum-up per Function Description while in the 2nd file isn't.
My problem is that I can't align the two files based on the quantity because I really need to see if there are variance between the two. Or if in case there are records in the First file that is not found in the 2nd File. Quantity should still be presented while 2nd file showing "0". This therefore could create a variance.
I'm not sure if I am positioning the join tool correctly but the result doesn't meet my expectation. I have attached references like a sample of results so that you may be able to visualize what I am looking for.
I'm attaching the following. Please feel free to correct my workflow.
1. 2 Input files - Function Summary (date is 7-20-21) and Corr Epic Production Detail Report
2. My Workflow
3. Sample of Expected Results (taken from manual tracker) - 2 Samples provided.
Looking forward for your consideration and assistance that would really add to my learning.
I think I know what you are are trying to do but you are going about it the wrong way... Are you looking to compare sums of some group (ie quantity variance) between the two files? You can't do it the way how you have it set up... You have quantity as a match criteria in your join meaning everything in your join anchor will have identical left and right quantities so your variance will be 0. Maybe I'm miss reading what you are trying to do but I think that is the route of your problem. I am working now on a fix.
Would I be correct then in assuming you'd view the first file as the key file and the second file as the master file? Let me try an updated version of my workflow with a left inner join so that you get those records not contain in the join...
O.k. just to be clear there is no variance in the sample data - but this SHOULD capture non-matching scenarios... Note - the left summarize is now redundant and has been removed. You'll capture the sums with the later summarize tool and you'll use that for compare.
I'm sorry but it's not me - it's your data. You have data issues. The workflow is fine... I'd imagine that your names don't match exactly so a record you think should be there isn't being included... This is on your end and you should track it down. Here is a screen grab of the value counts which are in the FALSE (un-matched entry)... note this is probably the same problem @Maskell_Rascal is having in communicating why his workflow works and yet you are not getting the results you'd expect... These are inconsistencies in your fields values.
I'm sorry for the confusion. Above 2 items is just a scenario example.
I've check both excels and have it filtered so that you may see the number of counts per function. Please see attached input files with highlighted in color. I've also manually type the "should be" results for your reference. That should be the results when we run Alteryx.
I have checked your revised workflow and the Corr Epic Sum is providing some incorrect figures. For Correspondent Prime Research under Epic, there should 12 instead of NULL. Please check my attachment file name as "This should be the results"
Please see attached references and let me know your thoughts.
Hoping for your continuing assistance on this matter.
updated version with the outer joins and variance where records don't occur. but here's why you won't get a variance of 12. These are your only two instance of Customer Prime Research. If you think you have more - you have a data problem.