topic Re: Showing the highest value in QlikView

Showing the highest value

Anonymous — Mon, 05 Nov 2018 10:31:34 GMT

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 !!

Re: Showing the highest value

tresB — Mon, 05 Nov 2018 10:49:22 GMT

In the script or UI?

Re: Showing the highest value

Anonymous — Mon, 05 Nov 2018 10:54:34 GMT

You can share solution for both the layers.

Re: Showing the highest value

niclaz79 — Mon, 05 Nov 2018 11:00:29 GMT

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;

Re: Showing the highest value

tresB — Mon, 05 Nov 2018 11:36:44 GMT

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;

Re: Showing the highest value

Anonymous — Mon, 05 Nov 2018 12:56:11 GMT

can something be done on UI level?

Re: Showing the highest value

tresB — Mon, 05 Nov 2018 13:21:21 GMT

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