topic Re: class/Rank/group in QlikView

class/Rank/group

Anonymous — Thu, 28 May 2015 06:31:47 GMT

Hi,

i need the sorting order for the particular range

please find the attachements

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 06:41:36 GMT

Suggestions pls

Re: class/Rank/group

danieloberbilli — Thu, 28 May 2015 06:46:15 GMT

as Qlik cant identify the value for a logic sorting order, you have to manually sort them: e.g. pre-load a table with the values in the correct sorting order, then load the real data and dropt the sorting table at the end.

Then you can use Load order in the Sorting tab of the chart object

SortOrder_tmp:

LOAD * INLINE [

RangeOfSales

less than 1k

1k-2k

3k-5k

6k-9k

10K-40K

1.00-4.00

5.00-900

10.00-15.00

];

Rangeofsales:

LOAD * INLINE [

SalesID, RangeOfSales

s01, 10K-40K

s02, less than 1k

s03, 3k-5k

s04, 1k-2k

s05, 6k-9k

s06, 1.00-4.00

s07, 10.00-15.00

s08, 5.00-900

];

DROP Table SortOrder_tmp;

Re: class/Rank/group

prajapatiamar38 — Thu, 28 May 2015 06:47:26 GMT

Hi...

In which order you want to sort.

Means first you want text or number.

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 06:50:25 GMT

i need them like this

RangeOfSales

less than 1k
1k-2k
3k-5k
6k-9k
10K-40K
1.00-4.00
5.00-900
10.00-15.00

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 06:51:52 GMT

Danie, what if i have more values for that column?

conside row count around 1k? we cant do manually i guess

Re: class/Rank/group

danieloberbilli — Thu, 28 May 2015 07:00:03 GMT

It only works, if you find some kind of 'logic' in the values to indicate Qlik how to sort...are there any other fields in your data source that might help?

Re: class/Rank/group

timanshu — Thu, 28 May 2015 07:04:01 GMT

Hi,

Row count doesn't matter. but no of range will be less than 20 I think.

Define range in if else and that will work.

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 09:20:15 GMT

Hi timanshu could you help me out sharing some sample app on the same?

Re: class/Rank/group

robert_mika — Thu, 28 May 2015 10:36:01 GMT

Look at Dual function

How to use- Dual()

Still manual input thru...

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 11:24:59 GMT

select RangeOfsales from table group by RangeOfsales order by

case

when RangeOfSales = Less than 1K' then 1

when RangeOfSales = '1k-2k' then 2

when RangeOfSales = '3k-5k' then 3

end asc;

how to use the abvoe query in QV. (in DB executing this query givig me the proper sort order, i need to take it to QV)

consider there are one or more columns like RangeOfsales to sort . ex: RangeofPrices ....

requierd help

Re: class/Rank/group

timanshu — Thu, 28 May 2015 11:52:18 GMT

load only(RangeOfsales ),pick(match(RangeOfsales ,'Less than 1K','1k-2k','3k-5k'),1,2,3) as grouping from (path of file) group by RangeOfsales;

including more fields on group by like RangeofPrices will not give right results.

ex:

rangeofsales, rangeofprices

1k-2k, 3k-5k

1k-2k,4k-8k

then output will only show

rangeofsales, rangeofprices,grouping

1k-2k, 3k-5k , 1

Re: class/Rank/group

Anonymous — Thu, 28 May 2015 12:26:38 GMT

so is there any alternative way to get the sort?

Re: class/Rank/group

timanshu — Thu, 28 May 2015 17:01:10 GMT

Simply do like this

Load if(sales>1000 and sales<2000, '1k-2k',

if(sales>2000 and sales<5000, '2k-5k',

if(sales>5000 and sales<10000, '5k-10k',

))) as range.

Re: class/Rank/group

ramoncova06 — Thu, 28 May 2015 17:18:43 GMT

you could use dual like Robert already suggested

how do you create your ranges ?

I am assuming with a nested if so you could do something like this

if (Sales < 1000, dual('less than 1k ',1),

if(Sales <= 2000, dual('1k-2k ',2),

if(Sales <= 5000, dual('3k-5k ',3),

.......

and then you apply a sort based on the numeric value of the field