Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Quantity Validation Based on Specified Date

marlontalisvistar
8 - Asteroid

Hi Guys,

 

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.

 

Thanks.

 

14 REPLIES 14
apathetichell
16 - Nebula

Hi,

 

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.

apathetichell
16 - Nebula

Maybe this is closer....

marlontalisvistar
8 - Asteroid

Hi Apathetichell,

 

I think you got what I am trying to imply. Now I know why I always got all records identical with variance "0". Just to add as an example using specific date:

 

1. The first file may contain records that is not contained in the 2nd file or vice versa and this is something that need to be capture.

2. File 1 - Custody Prokey-Classification Research - 20  / File 2 - Custody Prokey-Classification Research 0 = variance of 20

3. File 1 - Custody Prokey-Classification Research - 20  / File 2 - Custody Prokey-Classification Research 5 = variance of 15

4. File 1 - Custody Prokey-Classification Research - 15  / File 2 - Custody Prokey-Classification Research 20 = variance of -5

5. 4. File 1 - Custody Prokey-Classification Research - 0  / File 2 - Custody Prokey-Classification Research 20 = variance of -20

 

Looking forward for the solution. Thank you so much.

 

apathetichell
16 - Nebula

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...

apathetichell
16 - Nebula

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.

marlontalisvistar
8 - Asteroid

Hi Apathetichell,

 

Great work! It was able to find variance except for these:

 

1. File 1 - Custody Prokey-Classification Research - 20  / File 2 - Custody Prokey-Classification Research 0 = variance of 20

2. File 1 - Custody Prokey-Classification Research - 0  / File 2 - Custody Prokey-Classification Research 20 = variance of -20

 

Can you please make some correction?

 

Thanks and looking forward.

apathetichell
16 - Nebula

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.2021-07-30 (4).png

marlontalisvistar
8 - Asteroid

Hi apathetichell and anyone to help me please.

 

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.

Thanks.

apathetichell
16 - Nebula

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. 2021-07-31 (1).png

Labels