To find Max value from table based on group

Question

Hi,

I have below set of data. i want to find out max value based on group level data.

DeptRevenueHR10HR30HR20IT100IT200IT500IT300ADMIN3000ADMIN1000ADMIN2000

I am expecting below output in Max column to get "Y" & "N" next to their revenue based on dept level.

DeptRevenueMaxHR10NHR30YHR20NIT100NIT200NIT500YIT300NADMIN3000YADMIN1000NADMIN2000N

Thank you for the help.

binu_acs · Answer

@alt_tush One way of doing this is by assigning a record id to each field and joining based on the record id field

Max_1013.yxmd

DataNath · Answer

No problem @alt_tush. Whenever a request like this comes up, the usual approach is to do it this way as the join only pulls out the actual max and allows us to easily flag the 2 output streams. Part of the join is also [dept] so it'll only look for the max in each relevant department rather than across all revenues.

As a worst case scenario, if you're really against the join, you could perhaps just append the max's to all records and use a filter to remove those that don't match, before adding a formula to put the flag in. However, this will blow your data up and lead to a drop in performance, especially if your dataset is large.

alt_tush · Answer

Hi DatNath,

Thank you for your prompt reply.

Is there any way to avoid the join on Revenue column and get the same output. Because i don't want to join on revenue column as it is not the key field.

Thank you again :)