sorting horizontally

[Anonymous]

I have data in a table as shown below. I want to show maximum value out of L, W, and H as L. Is there any way I can sort the values in the row? Currently I am creating a single column, sort values there, and then reverse cross table.

Item	L	W	H
A	2	4	6
B	5	8	3
C	7	2	4
D	11	14	6
E	8	6	23

result table:

Item	L	W	H
A	6	4	2
B	8	5	3
C	7	4	2
D	14	11	6
E	23	8	6

Any help is much appreciated!

[Gysbert_Wassenaar · Accepted solution]

Like this:

LOAD *,

  If(L>L2 and L<H2,L, If(W>L2 and W<H2,W,H)) as W2;

LOAD

  *,

  RangeMin(L,W,H) as L2,

  RangeMax(L,W,H) as H2;

LOAD * INLINE [

    Item, L, W, H

    A, 2, 4, 6

    B, 5, 8, 3

    C, 7, 2, 4

    D, 11, 14, 6

    E, 8, 6, 23

];

If you want to do it in the chart with expressions you can use the same kind of rangemin, rangemax and if statements.

[Anonymous]

Thanks for your response Gysbert! I wait for a while and see if anybody has any other ideas.

[sunny_talwar]

Curious to know how else are you wanting to do this? I mean looking for a more efficient way? Looking for a front end solution?

[Anonymous]

Actually the way i was doing was lengthier than the Gysbert's one. Using Rangemin and Rangemax is definitely more optimized. As this is an open forum I am just waiting for a while before closing the thread if anybody else has any other ideas. 

[sunny_talwar]

I think it would be difficult to beat Gysbert's solution

I will try when I some free time though

[Anonymous]

yeaahh... I look fwd!

[sunny_talwar]

Here is my version

Table:

LOAD Item,

  RangeMax(L, W, H) as L,

  RangeMin(L, W, H) as H,

  RangeMin(RangeMax(L, W), RangeMax(H, W), RangeMax(L, H)) as W;

LOAD * INLINE [

    Item, L, W, H

    A, 2, 4, 6

    B, 5, 8, 3

    C, 7, 2, 4

    D, 11, 14, 6

    E, 8, 6, 23

];

[Anonymous]

Thanks for respecting my purpose! quite creative solution! I have just made  a small change to optimize yours. Hope you agree!

RangeMin(RangeMax(L, W), RangeMax(H, W)) as W;

Table:

LOAD Item,

  RangeMax(L, W, H) as L,

  RangeMin(L, W, H) as H,

  RangeMin(RangeMax(L, W), RangeMax(H, W)) as W;

LOAD * INLINE [

    Item, L, W, H

    A, 2, 4, 6

    B, 5, 8, 3

    C, 7, 2, 4

    D, 11, 14, 6

    E, 8, 6, 23

];

[sunny_talwar]

I try that and this might now work in circumstances where W comes in as the highest value. If that is never going to be the case then you are good to go. Else you will get weird numbers.

Added one additional row of data here:

Table:

LOAD Item,

  RangeMax(L, W, H) as L,

  RangeMin(L, W, H) as H,

  RangeMin(RangeMax(L, W), RangeMax(H, W), RangeMax(L, H)) as W,

  RangeMin(RangeMax(L, W), RangeMax(H, W)) as W1;

LOAD * INLINE [

    Item, L, W, H

    A, 2, 4, 6

    B, 5, 8, 3

    C, 7, 2, 4

    D, 11, 14, 6

    E, 8, 6, 23

    F, 15, 25, 19

];

And take a look at the output

I guess W is a safer solution, W1 might be slightly faster