How to see only the Top Sellers Based on Rating on table

[Anonymous]

I need some help , I am trying to create a table in which I am trying to show the highest seller symbol based on risk.

When I load this into qlikview , I see all but I want to see highest distinct level based on Risk. So for AA I just want to see Very High, for CC I just want to see High.

I would also like to be able to alternate between the two in a separate view, can someone please help, thousand thanks

Seller Symbol	Risk	Level
AA	High	3
AA	Low	1
AA	Very High	4
BB	Medium	2
CC	Medium	3
CC	High	3
DD	Low	1
DD	High	3
EE	Medium	2
FF	High	3
GG	High	3
GG	Medium	2

[mariusz_kumansk · Accepted solution]

dimension: symbol
expression:  firstsortedvalue(risk,-value)

//script

data:

LOAD

A as symbol,

B as risk,

C as value

FROM

(ooxml, no labels, table is Sheet1);

[luismadriz]

Hi,

If level goes hand in hand with risk, I think you can you this as a dimension

=AGGR(FirstSortedValue(Risk,-Level),[Seller Symbol])

What do you mean with Alternate between the two?

Luis

[mariusz_kumansk]

You can use alternative state for objects in your application

[Anonymous]

  can you explain what the calculation is meaning . so i can understand for future.   what is the purpose of - next to level  and what is firstsortedvalue do.   i got the results. but some of the results are missing. not all symbols showing up.

[Anonymous]

I am getting double results still when i put the formula in my table

[mariusz_kumansk]

application should working fine. Look at excel file because high and medium have the same level.

FirstSortedValue - chart function ‒ Qlik Sense

[luismadriz]

Hi Mike,

For the ones that are missing, it's because there are more than one value that meet the criteria. Level has incorrect values. You could get rid of Level as you have it and create a table like this that will associate automatically

Levels:

Risk,Level

Low,1

Medium,2

High,3

Very High, 4];

The correct answer is Mariusz' response once you have fixed the data

I hope this helps,

Cheers,

Luis

[luismadriz]

Oh forgot to say,

What I did using AGGR is a workaround to place something as a Dimension and not as a measure

Cheers,

Luis

[tamilarasu]

Hi Mike,

Script:

Data:
LOAD [Seller Symbol],
Risk,
Level,
Pick(Match(Risk,'Low','Medium','High','Very High'),1,2,3,4) as NewLevel
FROM
Source;

Front End Chart:

Dimension: [Seller Symbol]

Expression: FirstSortedValue(Risk,-NewLevel)

Result:

Note: If you have same duplicates in Seller Symbol and Risk columns, you can use "Distinct" in FirstSortedValue function.