How to group the multiple column values that result need to be place in one row.

[Gmurugan]

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

[acarter881]

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.

[Gmurugan]

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!

[flying008]

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

[AnnaMikhaylova]

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.

Spoiler

 

[Gmurugan]

I understand now, this is great! . Thank you so much

[AnnaMikhaylova · Accepted solution]

Hi @Gmurugan, if the response works for you, could you, please, mark it as a solution. Thank you.