Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

siva_boggarapu
Contributor II

How to make data in Ascending order?

Hello Everyone,

Col1Col2Col3Col4col5col7col7
100?120?160?100-120160
180?220?230?230180190220
189?200?240?189200240250

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to make data in Ascending order?

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.

12 Replies
giakoum
Honored Contributor II

Re: How to make data in Ascending order?

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

siva_boggarapu
Contributor II

Re: How to make data in Ascending order?

Hi Loannis,

Col1Col2Col3Col4col5col7col7



100-120160



230180190220



189200240250

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

Col1Col2Col3Col4col5col7col7
100?120?160?100-120160
180?220?230?230180190220
189?200?240?189200240250

Best,

Siva


giakoum
Honored Contributor II

Re: How to make data in Ascending order?

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

Still trying

MVP
MVP

Re: How to make data in Ascending order?

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.

siva_boggarapu
Contributor II

Re: How to make data in Ascending order?

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

MVP
MVP

Re: How to make data in Ascending order?

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.

siva_boggarapu
Contributor II

Re: How to make data in Ascending order?

Hi Jagan,

Thanks for your posting.

Col1col2col3 Col4Col5Col6Col7Col8Col9
156164- 156185190189189164156
205217- 205205205271.5271.5217224.5
408.5458.5- 408.5478-478478408.5458.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

siva_boggarapu
Contributor II

Re: How to make data in Ascending order?

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

siva_boggarapu
Contributor II

Re: How to make data in Ascending order?

Hello There,

How to found third Rangemin value?

Best,

Siva

Community Browser