Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Show the field name of the max value

Hello

Is there a way to compare multiple fields and find the field which has the highest value

Then after the highest value is found display the field name that value comes from

i.e.

ABC
1051

Max(A, B, C)

The highest value of the three is 10 in field A

Display A

9 Replies
Kushal_Chawda

rangemax(A,B,C)

MK_QSL
MVP
MVP

May be

Pick(Match(RangeMax(A,B,C),A,B,C),'A','B','C')

Anil_Babu_Samineni

How you are considering here, Because you used FieldName. Does you wanted to show where Max value of the FieldName??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
effinty2112
Master
Master

Hi Michael,

try:

Table:

LOAD

RecNo() as ID,

Round(Rand()*100) as A,

Round(Rand()*100) as B,

Round(Rand()*100) as C

AutoGenerate 10;

Cross:

CrossTable ('FieldName', 'Value') LOAD* Resident Table;

Left Join(Table)

Load

ID,

FirstSortedValue(FieldName,-Value) as MaxField

Resident Cross Group By ID;

ID A B C MaxField
1793166A
2952095
3804960A
479788C
5499018B
6157065B
7987241A
8798911B
9787773A
10155290C

Note the null for ID 2 since there is a tie and FirstSortedValue returns a null in such a case.

cheers

Andrew

Alternative

LOAD

RecNo() as ID,

Round(Rand()*100) as A,

Round(Rand()*100) as B,

Round(Rand()*100) as C

AutoGenerate 10;

Cross:

CrossTable ('FieldName', 'Value') LOAD* Resident Table;

MaxValues:

Left Keep(Cross)

Load

ID,

Max(Value) as Value

Resident Cross Group By ID;

Left Join(MaxValues)

LOAD * Resident Cross;

Drop Table Cross;

Then:

ID A B C Concat(Distinct FieldName,', ')
1554441A
2593079C
3669023B
425825B
522722A, C
6849915B
748628B
887472A
9315918B
10686091C
Anonymous
Not applicable
Author

Hello

I am looking to just show the Field Name

I want to find the field with the highest value

Then show the name of the field that value is in

dberkesacn
Partner - Creator III
Partner - Creator III

=FirstSortedValue(  Dim1 , -aggr(sum(Value),Dim1) )

Anonymous
Not applicable
Author

Hi Micheal,

For getting the Max sum use - =max(<Field name>) else =Aggr(Rank(sum(<Filedname>))=1,<Fieldname>)

For displaying the name of the Max field - if(Aggr(Rank(sum(<Filedname>))=1,<Fieldname>),<Fieldname>)

Thanks

Harshitha

Kushal_Chawda

try this

LOAD *,

        if(A=RangeMax(A,B,C),'A',

        if(B=RangeMax(A,B,C),'B',

        if(C=RangeMax(A,B,C),'C'))) as FieldName

Valerie
Contributor III
Contributor III

Hi Michael

Did you find the solution to this?