Showing the highest value

[Anonymous]

Hi All,

Need your help on understanding how i can get the following out from the current data :

   Current Data:

Codes	Risk rating	Billed amt
ABC	Medium	xxxx
ABC	High	xxxx
DEF	Low	xxxx
GHI	Low	xxxx
GHI	High	xxxx
GHI	Medium	xxxx
JKL	High	xxxx

Desired Output:

Codes	Risk rating	Billed amt
ABC	High	xxxx
DEF	Low	xxxx
GHI	High	xxxx
JKL	High	xxxx

I have 3 different ratings possible against a code i.e. High, medium or low. Codes can have multiple ratings assigned to them. What I want is that if a code has multiple rating assigned to it then only the highest available rating against that code is shown.

Thanks for your help in advance !!

[tresesco]

In the script or UI?

[Anonymous]

You can share solution for both the layers.

[niclaz79]

Hi, this should do it:

mRiskRating:

Mapping LOAD * Inline [

    Risk, Rating

    'Low', 1

    'Medium',2

    'High',3

];

Temp:

LOAD

    Codes,

    Max(RiskRatingNumber) as MaxRisk,

    Sum([Billed amt]) as [Billed amt]

Group by Codes;

LOAD

    Codes,

    ApplyMap('mRiskRating',[Risk rating]) as RiskRatingNumber,

    [Billed amt]

Resident source;

drop table source;

Final:

    Codes,

    If(MaxRisk = 1, 'Low',

          if(MaxRisk = 2, 'Medium', 'High')) as [Risk rating],

    [Billed amt]

Resident Temp;

drop table Temp;

[tresesco]

Try like:

Map:

Mapping Load * Inline [

Rating, Rank

High, 3

Medium, 2

Low, 1];

Tab1:

Load *,

ApplyMap('Map', "Risk rating") as RatingRank

Inline [

Codes, "Risk rating", "Billed amt"

ABC, Medium, AA

ABC, High, BB

DEF, Low, CC

GHI, Low, DD

GHI, High, EE

GHI, Medium, FF

JKL, High, GG];

NoConcatenate

Final:

Load

FirstSortedValue(Codes, -RatingRank) as Codes,

FirstSortedValue("Risk rating", -RatingRank) as "Risk rating",

FirstSortedValue("Billed amt", -RatingRank) as "Billed amt"

Resident Tab1 group by Codes;

Drop Table Tab1; 

[Anonymous]

can something be done on UI level?

[tresesco]

Yes, can be.  But not today (by me). I am logging off.