topic Re: Row no or Record no in QlikView

Row no or Record no

Tue, 09 Jun 2015 11:45:36 GMT

Hi,

I need the following data to be done with sort.

Re: Row no or Record no

Gabriel — Tue, 09 Jun 2015 11:52:44 GMT

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

Re: Row no or Record no

ToniKautto — Tue, 09 Jun 2015 11:54:33 GMT

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.

Re: Row no or Record no

Tue, 09 Jun 2015 11:56:09 GMT

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

Re: Row no or Record no

Tue, 09 Jun 2015 11:58:56 GMT

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

Re: Row no or Record no

Gabriel — Tue, 09 Jun 2015 12:13:09 GMT

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

Re: Row no or Record no

ToniKautto — Tue, 09 Jun 2015 12:17:28 GMT

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.

Re: Row no or Record no

Tue, 09 Jun 2015 12:21:19 GMT

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

Re: Row no or Record no

Tue, 09 Jun 2015 12:37:40 GMT

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

Re: Row no or Record no

Gabriel — Tue, 09 Jun 2015 12:43:22 GMT

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

Re: Row no or Record no

ToniKautto — Tue, 09 Jun 2015 12:57:25 GMT

It is naturally your choice, but I have a hard time seeing that nested IF statement would be a better or even equal way to maintain a long list of intervals. I think it will be worth your effort to do it the smartest way, even if if cost a little more effort.

Re: Row no or Record no

Tue, 09 Jun 2015 13:01:32 GMT

i can spare tme or effort on if/case but only the problem is after the QV reloads i could not see complete list of sorted values i even cant imagine whats wrong in the conditions

Re: Row no or Record no

ToniKautto — Tue, 09 Jun 2015 13:11:02 GMT

You will have to provide sample QVW for any input on what is going wrong, it will be impossible to guess without seeing the script.