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]

Rob -

Thanks for the idea, I will try to play around with FieldName() loop idea to see if I can come with a solution to get this done.

Best,

Sunny

[Anonymous]

Thanks Rob for your response. I will definitely give it a try tomorrow. My data has 153 date columns related to shipments of cargo. Dates are like arrived/departed dates. There are 17 other dimension fields. I hope you got the idea about my data.

[sunny_talwar]

This is not 100% like how Rob mentioned and will have to be played around with, but seems to work for the sample.

Table:

LOAD * INLINE [

    A, B, C

    10, 15, 23

    10, 16, 2

];

SET vMax = Pick(Match(RangeMax($1, $2, $3), $1, $2, $3), FieldName(1, 'Table'), FieldName(2, 'Table'), FieldName(3, 'Table'));

LET vField = FieldName(1, 'Table');

FinalTable:

LOAD *,

  $(vMax(A, B, C)) as Field

Resident Table;

DROP Table Table;

[Anonymous]

Thanks Sunny, I have not tried yet. I will try it tomorrow and then we discuss.

[sunny_talwar]

Sounds good, trying to refine it further

[sunny_talwar]

Now we are getting somewhere

Table:

LOAD * INLINE [

    A, B, C

    10, 15, 23

    10, 16, 2

];

LET vList = '$1';

LET vList2 = 'FieldName(1, ''Table'')';

For i = 2 to NoOfFields('Table')

  LET vList = '$(vList)' & ', $' & $(i);

  LET vList2 = '$(vList2)' & ', FieldName(' & $(i) & ', ''Table'')';

NEXT i

TRACE $(vList);

TRACE $(vList2);

SET vMax = Pick(Match(RangeMax($(vList)), $(vList)), $(vList2));

FinalTable:

LOAD *,

  $(vMax(A, B, C)) as Field

Resident Table;

DROP Table Table;

[sunny_talwar]

Final one for today

Table:

LOAD * INLINE [

    A, B, C

    10, 15, 23

    10, 16, 2

];

LET vList = '$1';

LET vList2 = 'FieldName(1, ''Table'')';

LET vList3 = FieldName(1, 'Table');

For i = 2 to NoOfFields('Table')

  LET vList = '$(vList)' & ', $' & $(i);

  LET vList2 = '$(vList2)' & ', FieldName(' & $(i) & ', ''Table'')';

  LET vList3 = '$(vList3)' & ', ' & FieldName($(i), 'Table');

NEXT i

TRACE $(vList);

TRACE $(vList2);

TRACE $(vList3);

SET vMax = Pick(Match(RangeMax($(vList)), $(vList)), $(vList2));

LET vField = FieldName(1, 'Table');

FinalTable:

LOAD *,

  $(vMax($(vList3))) as Field

Resident Table;

DROP Table Table;

[Anonymous]

u r awesome!