Hi all,
I am having some issues with a simple group by function in the summarize tool. When I separate each bank in its own summarize tool I get the proper output, however when trying to just use one summarize tool I get the below incorrect output. You can see Bank 3 is not grouping properly by the unit price. I need to group by service code, date and unit price by bank. I would like to avoid using separate summarize tools and then trying to re-join the data. I have included a screenshot of how I am currently trying to group/sum in the summarize tool.
Current incorrect output:
Service Code | Date | Bank 1 Volume | Bank 1 Unit Price | Bank 2 Volume | Bank 2 Unit Price | Bank 3 Volume | Bank 3 Unit Price |
12000 | 10/31/2020 | N/A | N/A | N/A | N/A | 3 | 5.5 |
12000 | 10/31/2020 | N/A | N/A | N/A | N/A | 16 | 10 |
12000 | 10/31/2020 | N/A | N/A | N/A | N/A | 34 | 30 |
12000 | 10/31/2020 | N/A | N/A | 3 | 0 | 3 | 30 |
12000 | 10/31/2020 | N/A | N/A | 5 | 22 | 5 | 30 |
12000 | 10/31/2020 | N/A | N/A | 3 | 50 | 3 | 30 |
12000 | 10/31/2020 | 4 | 46 | N/A | N/A | 4 | 30 |
Desired output:
Service Code | Date | Bank 1 Volume | Bank 1 Unit Price | Bank 2 Volume | Bank 2Unit Price | Bank 3 Volume | Bank 3 Unit Price |
12000 | 10/31/2020 | 4 | 46 | 3 | 0 | 3 | 5.5 |
12000 | 10/31/2020 | N/A | N/A | 5 | 22 | 16 | 10 |
12000 | 10/31/2020 | N/A | N/A | 3 | 50 | 49 | 30 |
@lomeoari
Can you upload your original workflow?
I could not figure out the logic here.🤗
Hi @lomeoari
in the current Summarize tool setting, There are nested “group by”, i.e. it asks Alteryx first to group by Unit 1 and then within each unique unit 1 further group by unit 2 and so on. That is why you are getting the so-called “incorrect” output (which is actually correct in the sense that the tool does exactly that it is asked to do”.
Without getting into whether multiple summarize tools and Joins are needed, what is the objective that you try to achieve? From the correct output sample, i am guessing that you try to list top 3 combinations of the price and volume for each bank unit which frankly does not quite make sense- so my guess must be wrong.
If you can share the sample/ mock data and explain the rationale / objective perhaps someone in the community can help better.
dawn