topic Re: sorting problem in QlikView

sorting problem

krishna20 — Sun, 26 Oct 2014 07:15:13 GMT

Hi friends ,

I'm facing sorting problem for calculated dimension.The below i mentioned the calculated dimension and the resulting one.

=if(CS_AMT_LC_1<0 ,'<0',

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,'1-500,000',

if(CS_AMT_LC_1>=500001 and CS_AMT_LC_1<=1000000,'500,001-1,000,000',

if(CS_AMT_LC_1>=1000001 and CS_AMT_LC_1<=2000000,'1,000,001-2,000,000',

if(CS_AMT_LC_1>=2000000 and CS_AMT_LC_1<=5000000,'2,000,001-5,000,000',

if(CS_AMT_LC_1>=5000001 and CS_AMT_LC_1<=10000000,'5,000,001-10,000,000',

if(CS_AMT_LC_1>10000000,'Above 10,000,000')))))))

Resulting one

1- 500,000

1,000,001 - 2,000,000

2,000,001 - 5,000,000

5,000,001 - 10,000,000

500 ,001 - 1,000,000

Above 10,000,000

Please suggest me how to resolve this.

Regards

Krishna

Re: sorting problem

anbu1984 — Sun, 26 Oct 2014 07:27:44 GMT

=if(CS_AMT_LC_1<0 ,Dual('<0',1),

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,Dual('1-500,000',2),

if(CS_AMT_LC_1>=500001 and CS_AMT_LC_1<=1000000,Dual('500,001-1,000,000',3),

if(CS_AMT_LC_1>=1000001 and CS_AMT_LC_1<=2000000,Dual('1,000,001-2,000,000',4),

if(CS_AMT_LC_1>=2000001 and CS_AMT_LC_1<=5000000,Dual('2,000,001-5,000,000',5),

if(CS_AMT_LC_1>=5000001 and CS_AMT_LC_1<=10000000,Dual('5,000,001-10,000,000',6),

if(CS_AMT_LC_1>10000000,Dual('Above 10,000,000',7))))))))

Re: sorting problem

krishna20 — Sun, 26 Oct 2014 07:35:25 GMT

Thank You Anbu

Re: sorting problem

danieloberbilli — Sun, 26 Oct 2014 07:42:38 GMT

Is it possible to move this part to the script?

When doing it within the script, you could create a field with these values (e.g. F1).

1) You load the field first without connection to the data but with the identical labels and in the right order. In loading it the first time and with the same name and fieldvalues - QV will take over this sorting order for the following 'real' field. In your object on the dashboard, choose as sorting: Load Order

e.g.

SortOrder_tmp:

LOAD * INLINE [

"1-500,000"

"500,001-1,000,000"

...and so on

];

2) Load/create 'real' field:

Data:

LOAD

if(CS_AMT_LC_1<0 ,'<0',

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,'1-500,000',

...and so on

as F1

FROM ...

3) Drop table SortOrder_tmp;