Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
A | B | C |
---|---|---|
10 | 5 | 1 |
Max(A, B, C)
The highest value of the three is 10 in field A
Display A
rangemax(A,B,C)
May be
Pick(Match(RangeMax(A,B,C),A,B,C),'A','B','C')
How you are considering here, Because you used FieldName. Does you wanted to show where Max value of the FieldName??
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 |
---|---|---|---|---|
1 | 79 | 31 | 66 | A |
2 | 95 | 20 | 95 | |
3 | 80 | 49 | 60 | A |
4 | 79 | 7 | 88 | C |
5 | 49 | 90 | 18 | B |
6 | 15 | 70 | 65 | B |
7 | 98 | 72 | 41 | A |
8 | 79 | 89 | 11 | B |
9 | 78 | 77 | 73 | A |
10 | 15 | 52 | 90 | C |
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,', ') |
---|---|---|---|---|
1 | 55 | 44 | 41 | A |
2 | 59 | 30 | 79 | C |
3 | 66 | 90 | 23 | B |
4 | 25 | 82 | 5 | B |
5 | 22 | 7 | 22 | A, C |
6 | 84 | 99 | 15 | B |
7 | 4 | 86 | 28 | B |
8 | 87 | 4 | 72 | A |
9 | 31 | 59 | 18 | B |
10 | 68 | 60 | 91 | C |
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
=FirstSortedValue( Dim1 , -aggr(sum(Value),Dim1) )
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
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
Hi Michael
Did you find the solution to this?