Join % to Quantity and Summarize

[Sman26]

Hi everyone, I'm a new Alteryx user and I'm trying to come up with a way to take a list of items by location, match it with a progress report submitted by another team, and then produce an estimate of inventory progress by type of item as well as overall for all items. I've attached the Alteryx workflow I'm trying to work on.

The problems I'm running into are that I can sum the total of items in one worksheet to get a list of how many things there are total, but I can't get a function to work that will multiply the totals by area by the percentage completion for that area, then sum those up and divide the result by the total number of items overall for that category to get a completion %. I previously ran into issues where the null values returned on the join weren't allowing me to do numeric calculations, but flipping the join seems to have fixed that issue.

I've been able to solve this previously using SQL joins and then just using excel to multiple the % complete column by the total # of items per area column, and then doing a sum of the total #s complete / sum of total # of items columns, but this isn't 100% automatic and I can't pass it off to non-technical teams as a standalone tool.

Here's an example of what kind of data I'm dealing with:

The yellow section is one report that I've created using SQL and a list of all the inventory items. In reality this is multiple files, one for each category of items. The red is a rough progress report I get separately from another team. And the blue is what I'm trying to automate.

https://docs.google.com/spreadsheets/d/1XKFP3yaHatVCTA1cZlijkSHVHvI8xMstmH3siTbsfRA/edit?usp=sharing

[Aguisande · Accepted solution]

Hi @Sman26 

Here is how the workflow looks (You can download a copy below this post):

The run down (Note that there are 4 branches after the first join, that's because the second one is the same logic (Copy& Paste of the two first, but removing the Group By Clauses in both Summarize Tools to get the All Items Results):

- Reading both data sources, and do some cleaning on the Inventory Completion to be able to use the values

- The join remains as in your example (both data sources by Area)

- Once you got the join results on Area, you must split your data stream: 1 to get SUM of [On Hand] per [Item Type] and the other, to get the actual completions on the Items. That's why I added a formula before the Summarize tool to get  [Total Number on Hand] * [Inventory Completion] and the summarize this result by Item Type.

- Having those values, just join them to get the On Hand and the Real Completion per Item Type

- Calculate the desired output dividing Real Completion / Real On Hand and you got the values per Item Type.

At the end, just UNION both partial results into one unique Output

Hope this helps.

[Aguisande]

Hi @Sman26 

Did the solution proposed worked for you?

If it did, please mark it a a Solution, so other users with the same problem can find it.

If not, please let me know and I can see how to help.

Best,

_AG_

[Sman26]

Hey, sorry about the late response. I got hit with something else but I just looked through it. First, this is amazing. I hadn't thought about those graphs, and it's really helpful how they adjust based on however many categories are found.

I added an output to a file and it prints it out exactly how you'd expect and the math works out. I threw some of my actual data in there and things went a little weird with the math (I think I need to doublecheck the column names and stuff because it works fine with the test data) but it categorized everything correctly and summed up the total quantities correctly as well.

[Sman26]

Hey aguisande, just a note but when I added in my actual data some of the numbers weren't coming out right even though it was calculating the percentages correctly based on those incorrect totals. It looks like I just had to change the left joins to a join multiple to get it working since I guess left joins in SQL and Alteryx work slightly differently.

[Aguisande · Accepted solution]

What I think is happening is that you're "missing" some incoming values in the JOIN (grab a couple of Browse Tools and connect them to the L and R outputs of your join tool)

L & R Outputs in Alteryx's Join tool are UNJOINED records.

You must UNION the L output with the J output to get what a LEFT JOIN'd produce

[Sman26]

Yeah, I think that's what it was. It's working now anyway. I manually calculated the results a few times to double check and it's coming up with the correct output. This has been a huge time saver so thanks so much.