topic Re: How to make data in Ascending order? in QlikView

How to make data in Ascending order?

siva_boggarapu — Wed, 27 Jun 2012 06:03:41 GMT

Hello Everyone,

Col1	Col2	Col3	Col4	col5	col7	col7
100?	120?	160?	100	-	120	160
180?	220?	230?	230	180	190	220
189?	200?	240?	189	200	240	250

I have one strght table like above. I have data only Col4,Col5,col6 and col7. Here My question is I have to fill this data based on selection of these four fileds. I need to fill Col1,col2, col3 in Ascending order?

How it is possible?

Best,

Siva.

Re: How to make data in Ascending order?

giakoum — Wed, 27 Jun 2012 06:28:13 GMT

It is not clear at all what you are trying to achieve. Please explain.

Re: How to make data in Ascending order?

siva_boggarapu — Wed, 27 Jun 2012 07:00:39 GMT

Hi Loannis,

Col1	Col2	Col3	Col4	col5	col7	col7
			100	-	120	160
			230	180	190	220
			189	200	240	250

I have one straight table like above. I don't have data for Col1,Col2 and Col3. I should get data for these fileds from Col4,Col5, Col6 and Col7.

In first row Col 4,Col5,Col6 and Col7 we have data 100,-,120 and 160 respectively. In this 100,120 and 160 are three minimum values. So, I have to put these three minimum values in Col1,Col2 and Col3.

How Can I put expression for this?

I want to o/p like Below

Col1	Col2	Col3	Col4	col5	col7	col7
100?	120?	160?	100	-	120	160
180?	220?	230?	230	180	190	220
189?	200?	240?	189	200	240	250

Best,

Siva

Re: How to make data in Ascending order?

giakoum — Wed, 27 Jun 2012 07:59:14 GMT

Col1 is easily achieved by rangemin but Col2 and 3 I haven't found anything yet

Still trying

Re: How to make data in Ascending order?

jagan — Wed, 27 Jun 2012 08:35:34 GMT

Hi Siva,

It is bit difficult to find second and Third min values in expression for given values. We need to find a logic using IF statements.

Hope attached file helps you. I calculated column A only.

Regards,

Jagan.

Re: How to make data in Ascending order?

siva_boggarapu — Wed, 27 Jun 2012 09:08:57 GMT

Hi Jagan,

Thank you very much. Its working for Col1 and I need same thing for col2 and col3?

Can anyone help on this? Its very urgent.

Best,

Siva

Re: How to make data in Ascending order?

jagan — Wed, 27 Jun 2012 09:33:26 GMT

Hi Siva,

I some how managed to get the result, I think it is not optimized way but there is no option. Let us wait if any of our friends come out with any other logic.

Please find attachment for solution.

Regards,

Jagan.

Re: How to make data in Ascending order?

siva_boggarapu — Wed, 27 Jun 2012 12:13:00 GMT

Hi Jagan,

Thanks for your posting.

Col1	col2	col3		Col4	Col5	Col6	Col7	Col8	Col9
156	164	-	156	185	190	189	189	164	156
205	217	-	205	205	205	271.5	271.5	217	224.5
408.5	458.5	-	408.5	478	-	478	478	408.5	458.5

As per your report I am getting Col1 and Col2 values what I expected. But for col3 I am getting as same of Col1 values

Best,

Siva

Re: How to make data in Ascending order?

siva_boggarapu — Thu, 28 Jun 2012 04:05:29 GMT

Hi Jagan,

I am putting expression like below

RangeMin(

If(Match($(vMarketFare1), MKT1, MKT2), Null(), $(vMarketFare1)),

If(Match($(vMarketFare2), MKT1, MKT2), Null(), $(vMarketFare2)),

If(Match($(vMarketFare3), MKT1, MKT2), Null(), $(vMarketFare3)),

If(Match($(vMarketFare4), MKT1, MKT2), Null(), $(vMarketFare4)),

If(Match($(vMarketFare5), MKT1, MKT2), Null(), $(vMarketFare5)),

If(Match($(vMarketFare6), MKT1, MKT2), Null(), $(vMarketFare6))

)

Will it work for variables?

Best,

Siva

Re: How to make data in Ascending order?

siva_boggarapu — Thu, 28 Jun 2012 07:49:30 GMT

Hello There,

How to found third Rangemin value?

Best,

Siva

Re: How to make data in Ascending order?

jagan — Thu, 28 Jun 2012 07:54:26 GMT

Hi Siva,

Can you attach what you declared for variables?

vMarketFareX = ?

Regards,

Jagan.

Re: How to make data in Ascending order?

siva_boggarapu — Thu, 28 Jun 2012 08:35:40 GMT

Hi Jagan,

1)RangeMin($(vMarketFare1),$(vMarketFare2),$(vMarketFare3),$(vMarketFare4),$(vMarketFare5),$(vMarketFare6))--1st Expression

2)RangeMin(If(MKT1 = $(vMarketFare2), Null(), $(vMarketFare2)), If(MKT1 = $(vMarketFare3), Null(), $(vMarketFare3)), If(MKT1 = $(vMarketFare4), Null(), $(vMarketFare4)), If(MKT1 = $(vMarketFare5), Null(), $(vMarketFare5)),If(MKT1 = $(vMarketFare6), Null(), $(vMarketFare6)))--2nd expression

RangeMin(

If(Match($(vMarket1), MKT1, MKT2), Null(), $(vMarket1)),

If(Match($(vMarket2), MKT1, MKT2), Null(), $(vMarket2)),

If(Match($(vMarket3), MKT1, MKT2), Null(), $(vMarket3)),

If(Match($(vMarket4), MKT1, MKT2), Null(), $(vMarket4)),

If(Match($(vMarket5), MKT1, MKT2), Null(), $(vMarket5)),

If(Match($(vMarket6), MKT1, MKT2), Null(), $(vMarket6))

)-- 3rd expression

Best,

Siva

Re: How to make data in Ascending order?

jagan — Thu, 28 Jun 2012 09:38:13 GMT

Hi Siva,

Try like this, hope it works

RangeMin(

If(($(vMarket1) = MKT1 OR $(vMarket1) = MKT2), Null(), $(vMarket1)),

If(($(vMarket2) = MKT1 OR $(vMarket2) = MKT2), Null(), $(vMarket2)),

If(($(vMarket3) = MKT1 OR $(vMarket3) = MKT2), Null(), $(vMarket3)),

If(($(vMarket4) = MKT1 OR $(vMarket4) = MKT2), Null(), $(vMarket4)),

If(($(vMarket5) = MKT1 OR $(vMarket5) = MKT2), Null(), $(vMarket5)),

If(($(vMarket6) = MKT1 OR $(vMarket6) = MKT2), Null(), $(vMarket6))

)

Regards,

Jagan.