retrieve name of field corresponding to range max value

[Anonymous]

Hi all,

Wondering if there is any way to retrieve field name associated with range max value. I don't want to use crosstable and then firstsorted value.

A	B	C	Field
10	15	23	C
10	16	2	B

Thanks

[sunny_talwar · Accepted solution]

May be this

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

[sunny_talwar]

Here is a sample from your data

[Anonymous]

This is a good solution. Sunny, there are more than 150 field that's why i didn't want to do crosstable. Here also i will have to write all fields name. Any alternate direct function in your knowledge?

[Anonymous]

i mean similar to firstsorted value in array

[sunny_talwar]

Not sure bro. I have never seen a function pull field name using a value within the field. I mean there are FieldValue() functions, but not sure how they can be used here. I will keep testing and let you know

[Anonymous]

I tried all fieldsuffix function, not worked...but thanks.

[sunny_talwar]

I will keep digging. May be swuehl‌ can offer his expert advice also

[rwunderlich]

You don't necessarily have to list all fieldnames in a cross table. You can use "*". What does your data look like?

[rwunderlich]

You can generate the pick(match()) dynamically in a chart as:

=Pick(Match(RangeMax(

$(=concat([$Field],','))

),

$(=concat([$Field],','))

),

$(=

chr(39)

& concat([$Field], chr(39) & ',' & chr(39))

& chr(39)

)

)

You can limit the field list to a particular table by adding a bit of <$Table={xyz}> set analysis into the concat(). Or exclude certain fields.

You can do something similar in the script. but yu would have to build up the field list in a variable loop using the fieldname() function. A bit more work.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com