4 Replies Latest reply: Mar 11, 2013 10:29 AM by Martha Misquitta

# Display max value and other related fields

Hello everyone,

I need help in accomplishing the final product.  See step by step information below:

# Raw data:

Name
Dept
Dept % 1
Dept % 2
Role
MariaLT25%80%Core
MariaBT55%0%Embed
JohnLT75%25%Embed
LisaCT0%100%Core
LisaLT60%20%Embed

# Step 1:

Name Dept Dept % 1 Dept % 2 =IF(Dept % 2 >=0.35, '>35%','<35%') Role 1 2 3 4 5 6 Maria LT 25% 80% >35% Core Maria BT 55% 0% <35% Embed John LT 75% 25% <35% Embed Lisa CT 0% 100% >35% Core Lisa LT 60% 20% <35% Embed

# Step 2:

If duplicate name, select the row that has max value = (Dept %1 + Dep % 2).

# Step 3: FINAL:

After eliminating duplicates,

Count name by role

Final Product:

Dept
=IF(Dept % 2 >=0.35, '>35%','<35%')
Count of Name by role = Core
Count of name by role = Embed
BT>35%1
JT>35%
1
LT<35%1

>35%1
• ###### Re: Display max value and other related fields

Hi,

You should do the group by Name, then find max out of that in the script only and then do the join with your main table with resident table which contain few columns in that.

Regards,

Nirav Bhimani

• ###### Re: Display max value and other related fields

Hi,

HTH

Sushil

• ###### Re: Display max value and other related fields

Thank you Sushil for making time to help me with this.  This worked.

• ###### Re: Display max value and other related fields

Thank you Sushil for making time to help me.  This worked.