topic Re: sorting horizontally in QlikView

sorting horizontally

Anonymous — Mon, 23 May 2016 15:25:27 GMT

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!

Re: sorting horizontally

Gysbert_Wassenaar — Mon, 23 May 2016 18:01:00 GMT

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.

Re: sorting horizontally

Anonymous — Mon, 23 May 2016 18:05:18 GMT

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

Re: sorting horizontally

sunny_talwar — Mon, 23 May 2016 18:21:27 GMT

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?

Re: sorting horizontally

Anonymous — Mon, 23 May 2016 18:26:29 GMT

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.

Re: sorting horizontally

sunny_talwar — Mon, 23 May 2016 18:34:26 GMT

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

I will try when I some free time though

Re: sorting horizontally

Anonymous — Mon, 23 May 2016 18:39:46 GMT

yeaahh... I look fwd!

Re: sorting horizontally

sunny_talwar — Mon, 23 May 2016 18:56:46 GMT

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

];

Re: sorting horizontally

Anonymous — Mon, 23 May 2016 19:14:51 GMT

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

];

Re: sorting horizontally

sunny_talwar — Mon, 23 May 2016 19:19:14 GMT

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

Re: sorting horizontally

Anonymous — Mon, 23 May 2016 19:36:08 GMT

yup I agree...taking 2 combinations is a flawed approach!