topic Sorting in Charts in QlikView

Sorting in Charts

Thu, 22 Dec 2011 10:02:02 GMT

Hi All,

In my application all the charts for sorting am using the below approach, where I have to sort values of a chart in following order

‘Others’ should be in the last always.

What I have done here is I have assigned each and every column with a rank no and in sorting properties used conditional sorting for “only(rank)”.

LOAD * INLINE [
    LOC CODE, RANK
    CLD, 1
    CTC, 2
    ODD, 3
    SDC, 4
    Plant, 5
    Other, 6
];

But this is causing more inline tables getting created inTable Viewer. whether this is correct way to perform this sorting or is there any other better way. Sorting is to be implemented in almost every chart in my application,So please help me this....

Regards,

Anil

Sorting in Charts

vijay_iitkgp — Thu, 22 Dec 2011 10:07:56 GMT

Hi ,

You can use sorting by expression and use if([LOC CODE]='CLD',1,If(........)

Hope this will help you.

Regards

VIjay

Re: Sorting in Charts

sridhar240784 — Thu, 22 Dec 2011 10:56:45 GMT

Hi,

One more option would be loading this inlinen table as mapping table and use Apply Map() function & Dual function to sort your chart. So that you`ll not see many inline tables in table viewer.

Check the attached application.

Hope this helps you.

-Sridhar

Sorting in Charts

rohit214 — Thu, 22 Dec 2011 10:58:51 GMT

try this

if(pick(match([LOC CODE] ,'CLD', 'CTC', 'ODD' ,'SDC', 'Plant','Other'),'1','2','3','4','5','6')

thanks &regards

rohit

Sorting in Charts

jagan — Thu, 22 Dec 2011 12:36:25 GMT

Hi Anil,

Instead of using =only(RANK) try using RANK in Expression section and select Ascending or descending. I usually do like this. It works.

Regards,

Jagan.

Sorting in Charts

Thu, 22 Dec 2011 13:10:01 GMT

Thanks for you replies guys......

@ Jagan: here the problem is i want to avoid inline tables

@Rohit: Your expression is giving error this exp am directly using in sort expression

@Srdiahr: From your option any way inline tables getting created in table viewer.

@Vijay: i have tried you expresssion its also giving error could yyou please give me full exp.

Sorting in Charts

jagan — Thu, 22 Dec 2011 13:14:54 GMT

Hi,

Try putting the Inline table in the last tab. I also faced the same and moved the inline code to end of the script.

Hope this helps you.

Regards,

Jagan.

Sorting in Charts

vijay_iitkgp — Thu, 22 Dec 2011 13:15:15 GMT

Hi,

In sort expression you can write

If( LOC CODE ='CLD', 1,

If( LOC CODE ='CTC, 2,

If( LOC CODE ='ODD, 3,

If( LOC CODE ='SDC, 4,

If( LOC CODE ='Plant,5,

If( LOC CODE ='Other,6))))))

And sort it in ascending order.

Regards

VIjay

Re: Sorting in Charts

sridhar240784 — Thu, 22 Dec 2011 13:32:26 GMT

Hi,

If you use a Mapping load, table will not be shown in table viewer.

Below is the explaination copied from QV Help.

"The mapping prefix to a Load or Select statement is used for mapping a certain field value or expression to the values in the first column of the mapping table. A mapping table consists of two columns, the fist containing comparison values and the second containing the desired mapping values. Mapping tables will be stored temporarily in the memory and dropped automatically, after script execution."

-Sridhar

Sorting in Charts

Thu, 22 Dec 2011 14:13:29 GMT

Hi,

@Jagan: if we move inline code to last tab any way tables are getting created am i right?

@ Vijay: still showing error in expression look into the below screen shot

@Sridhar: I have tried you approach its working correctly and this is ok for limited records but if we have 100-1000 records in that case how to do????

If am wrong please correct me

Regards,

Anil

Sorting in Charts

sridhar240784 — Thu, 22 Dec 2011 14:32:40 GMT

Oops...!! I`m not sure, whether i understood your problem correctly or not. you are comfortable to write pages and pages of if condition when you have 100 to 1000 code but not comfortable to create a inline table .

Do have any rules to create a sort order? if so.. share us the rules for sorting order, will try to create the dynamic solution.

Hope i`m clear.

-Sridhar

Sorting in Charts

Fri, 23 Dec 2011 11:58:15 GMT

Vijay now its working perfectly....i have changed a bit in the exp like this

If([LOC CODE] ='CLD', 1,(If( [LOC CODE] ='CTC', 2,(If( [LOC CODE] ='ODD', 3,(If( [LOC CODE] ='SDC', 4,(If( [LOC CODE] ='Plant',5,(If( [LOC CODE] ='Other',6)))))))))))

Sridhar i confused a bit with your answer but it is working from you way also

i think above exp is the easy to use for sorting what do you say guys.........

Guys thanx for all your replies and suggestions.......

Regards,

Anil

Re: Sorting in Charts

johnw — Tue, 27 Dec 2011 21:54:46 GMT

Do the inline load at the top of the script, leave the RANK field out of it, and then drop the table at the end of the script. Then you can sort by original load sequence. I do that all the time.

[Location Sort Order]:
LOAD * INLINE [
LOC CODE
CLD
CTC
ODD
SDC
Plant
Other
];

// rest of your script here

DROP TABLE [Location Sort Order];

You'll have to reload to change the sort order, which might be a drawback, but it's then easy to use that same sort order everywhere. If you really want a chart expression for some reason, the match() function returns the sequence number of the first match, so this should work as the sort expression.

match([LOC CODE],'CLD','CTC','ODD','SDC','Plant','Other')