Row no or Record no

[#1]

Hi,

I need the following data to be done with sort.

[Gabriel]

Hi,

I think you can you DUAL() Function for  this purpose.

IF(RANK < 1, DUAL('NAGATIVE',1)

     , RANK >= 1 AND RANK <= 1000, DUAL('Less than 1K',2)

      , RANK >= 1000 AND RANK <= 4000, DUAL('1K -4K',3)
           , RANK >= 5000 AND RANK <= 9000, DUAL('5K -9K',3)

               ,E.T.C)          AS Rank

[ToniKautto]

I do not understand what you are trying to do. Where do you plan to execute the sort? Why is the sort order relevant?

If you are looking at applying this as a field in your script, then I would suggest looking at interval match.

For a more detailed reply, please provide some more detaisl on the datat you have, and where you need to do the sort.

[#4]

Thanks Gabriel,

I tried the same but QV is giving 2 or 3 records in the sorting.

Could you please try in the QV and load the column?

i just need to cross check whether i have committed any mistake

[#5]

Thanks Toni for the reply,

Yes i have values that shows the price ranges of :

1-100,

100-999

1k-2k

3k-5k

so on. but these are not in the proper order, i need these values to show them in a list box as a filter where user can select the ranges to see how many products sold in that range.

i hope you get the requirement

[Gabriel]

Hi,

Try change the range in my script to fit your purpose

if(Rank < 1, dual('Nagetive',1),

if(Rank >= 1 AND Rank <= 4000, dual('1K',2),

if(Rank >= 1000 AND Rank <= 4999, dual('2K - 4K',3)

,if(Rank >= 5000 AND Rank <= 9000, dual('5K - 9K',4)

,if(Rank >= 5000 AND Rank <= 9999, dual('10K - 19K',5)

))))) AS RANK

[ToniKautto]

My suggestion is to use IntervalMatch to link the prices to the ranges.

In this context simply enumerate the range label so that you can use numeric sort in your list box. There is no need to ave the numbers going from 1-14, just use the lower limit as the number to set the intervals in order.

Also keep in mid that the range can not overlap, so the upper limit on one interval can not be identical to the lower limit on the following interval.

[#8]

Hi Gabriedl,

Thanks again but it is not giving me the proper results when i reload the QV and taken it into UI as a listbox

[#9]

Thanks Toni

the solution is correct but not suits to my correct scenario

1) i cant use join now as i have many other joins

2) i have two more fields to do the same kind of sorting too

[Gabriel]

Hi,

The problems is in how you have defined your rank in excel.

If you change that to 1000 - 4000 instead of K it will work with my suggestions