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

sorting horizontally

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.

    

ItemLWH
A246
B583
C724
D11146
E8623

result table:

    

ItemLWH
A642
B853
C742
D14116
E2386

Any help is much appreciated!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

];


Capture.PNG

Anonymous
Not applicable
Author

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

Capture.PNG

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