How to add the unwanted sales value with max sales value of each Group by country data

[Naps_230]

Hi ,

Here i provide source and output file.

Requirement: i need to add XX sales value with max value of every group wise country sales.

ex: In first column Airtel ,India have max sales is 220, and xx sales is 20 , So we need add those two value(220+20) =240

it should work with every column(Airtel,BSNL,Jio and Grand total), then xx row will be remove from table, Date field also.

OUT PUT:

Country	Airtel	BSNL	JIO	Grand Total
AUS	80	343	100	465
IND	240	150	90	460
PAK	185		360	623
Grand Total	505	493	550	1548

Source data:

Country	Group	Sales	Date
IND	Airtel	100	13/1/2022
AUS	BSNL	200	13/1/2022
PAK	JIO	300	13/1/2022
IND	BSNL	150	14/1/2022
AUS	Airtel	80	14/1/2022
PAK	Airtel	90	14/1/2022
IND	Airtel	120	13/1/2022
AUS	BSNL	85	13/1/2022
PAK	Airtel	95	13/1/2022
XX	Airtel	20	13/1/2022
XX	BSNL	58	13/1/2022
XX	JIO	60	13/1/2022
IND	JIO	90	13/1/2022
AUS	JIO	100	13/1/2022

After pivoting above data , we get like below:

Sum of Sales	Group
Country	Airtel	BSNL	JIO	Grand Total
AUS	80	285	100	465
IND	220	150	90	460
PAK	185		300	485
XX	20	58	60	138
Grand Total	505	493	550	1548

As per the my current requirement, output should be like below:

OUT PUT:

Country	Airtel	BSNL	JIO	Grand Total
AUS	80	343	100	465
IND	240	150	90	460
PAK	185		360	623
Grand Total	505	493	550	1548

[JordyWegman]

Hi Naps,

I don't have the possibility to test this now, but conceptually I would do the following:

• Load your data in the back-end
• Determine the highest value for each country per group
• Rename XX to the highest country per group (probably with a mapping load)

Jordy

Climber

[Naps_230]

Hi Jordy,

Thanks for suggestion, if you give  more details or testing the data in your system.it will help for me.

Regards,

Naps