Hi ,
I have the usecase to group multiple column value with certain condition. Below is the scenario
Under CLX category, i need only LAX city ( green) to show but Sum_F should be 35 (need to sum all sum_F- CLX value (13+1+0+21)). Other rows and columns should present as it is. Grouping required only Sum_F not all . Red row not required.
This grouping required only CLX category not for PNW/OAK. Please any suggestion to achieve this use case
Thanks
Geetha
Solved! Go to Solution.
Hello, @Gmurugan.
It seems like you can use the Summarize tool to group by category and sum "Sum_F" then use the Filter tool on your original dataset to exclude cities that are not equal to "LAX" with a category of "CLX" then use a Join tool on the summarized total and your filtered data. You may need to define some grouping for your dates as well, such as adding a new field that includes the month and including that in your grouping within the Summarize tool.
Hi @acarter881 ,
I tried with Summarize tool but facing issue when am trying to exclude the red row. Am not sure what I missed. can you please provide some workflow, it will help me to understand better.
thank you!
Hi, @Gmurugan
FYI.
Input | ||||||||||||
Date | Category | City | Next | code | number | Sum_A | Sum_B | SUM_C | Sum_D | Sum_E | Sum_F | |
2023-3-21 | PNW/OAK | TAC | HON | abc | 481 | 305 | 38 | 11 | 4 | 0 | 0 | |
2023-3-21 | PNW/OAK | OAK | HON | abc | 481 | 333 | 69 | 25 | 45 | 0 | 0 | |
2023-3-21 | PNW/OAK TOTAL | abc | 481 | 638 | 107 | 36 | 49 | 0 | 0 | |||
2023-4-4 | PNW/OAK | TAC | HON | abc | 482 | 315 | 36 | 8 | 9 | 0 | 0 | |
2023-4-4 | PNW/OAK | OAK | HON | abc | 482 | 317 | 75 | 26 | 34 | 0 | 0 | |
2023-4-4 | PNW/OAK TOTAL | abc | 482 | 632 | 111 | 34 | 43 | 0 | 0 | |||
2023-5-17 | CLX | SHA | HON | abc | 483 | 11 | 0 | 0 | 0 | 0 | 13 | |
2023-5-17 | CLX | NAH | HON | abc | 483 | 1 | 0 | 0 | 0 | 0 | 1 | |
2023-5-17 | CLX | LAX | HON | abc | 483 | 690 | 234 | 21 | 50 | 0 | 0 | |
2023-5-17 | CLX | GUM | HON | abc | 483 | 4 | 0 | 0 | 0 | 0 | 21 | |
2023-5-17 | CLX TOTAL | abc | 483 | 706 | 234 | 21 | 50 | 0 | 35 | |||
2023-8-9 | CLX | GUM | NAH | abc | 485 | 8 | 0 | 0 | 0 | 0 | 20 | |
2023-8-12 | CLX | NAH | NGB | abc | 485 | 2 | 0 | 0 | 0 | 0 | 6 | |
2023-8-16 | CLX | SHA | LAX | abc | 485 | 22 | 0 | 0 | 0 | 0 | 27 | |
2023-8-30 | CLX | LAX | HON | abc | 485 | 703 | 171 | 17 | 66 | 1 | 1 | |
2023-8-30 | CLX TOTAL | abc | 485 | 735 | 171 | 17 | 66 | 1 | 54 | |||
Output | ||||||||||||
Date | Category | City | Next | code | number | Sum_A | Sum_B | SUM_C | Sum_D | Sum_E | Sum_F | Sum_DF |
2023-3-21 | PNW/OAK | TAC | HON | abc | 481 | 305 | 38 | 11 | 4 | 0 | 0 | |
2023-3-21 | PNW/OAK | OAK | HON | abc | 481 | 333 | 69 | 25 | 45 | 0 | 0 | |
2023-3-21 | PNW/OAK TOTAL | abc | 481 | 638 | 107 | 36 | 49 | 0 | 0 | |||
2023-4-4 | PNW/OAK | TAC | HON | abc | 482 | 315 | 36 | 8 | 9 | 0 | 0 | |
2023-4-4 | PNW/OAK | OAK | HON | abc | 482 | 317 | 75 | 26 | 34 | 0 | 0 | |
2023-4-4 | PNW/OAK TOTAL | abc | 482 | 632 | 111 | 34 | 43 | 0 | 0 | |||
2023-5-17 | CLX | SHA | HON | abc | 483 | 11 | 0 | 0 | 0 | 0 | 13 | |
2023-5-17 | CLX | NAH | HON | abc | 483 | 1 | 0 | 0 | 0 | 0 | 1 | |
2023-5-17 | CLX | LAX | HON | abc | 483 | 690 | 234 | 21 | 50 | 0 | 0 | 35 |
2023-5-17 | CLX | GUM | HON | abc | 483 | 4 | 0 | 0 | 0 | 0 | 21 | |
2023-5-17 | CLX TOTAL | abc | 483 | 706 | 234 | 21 | 50 | 0 | 35 | |||
2023-8-9 | CLX | GUM | NAH | abc | 485 | 8 | 0 | 0 | 0 | 0 | 20 | |
2023-8-12 | CLX | NAH | NGB | abc | 485 | 2 | 0 | 0 | 0 | 0 | 6 | |
2023-8-16 | CLX | SHA | LAX | abc | 485 | 22 | 0 | 0 | 0 | 0 | 27 | |
2023-8-30 | CLX | LAX | HON | abc | 485 | 703 | 171 | 17 | 66 | 1 | 1 | 54 |
2023-8-30 | CLX TOTAL | abc | 485 | 735 | 171 | 17 | 66 | 1 | 54 |
Hi @Gmurugan
If you first filter out all the TOTAL rows, then use summarise tool (but without grouping by City or Next) you will calculate Totals. You then can isolate using Formula tool your total CLX result and use them both as CLX line item and total. You then will need to filter existing CLX data from your data set and union both CLX as Totals and as LAX back. Then you can use Union tool to join newly calculated totals to your filtered data (just pay attention to the column names, so it unions by Name correctly, and then a small trick of using summarise tool again to get the rows in correct order. I attached a possible solution.
I understand now, this is great! . Thank you so much
Hi @Gmurugan, if the response works for you, could you, please, mark it as a solution. Thank you.