9 Replies Latest reply: Sep 19, 2011 3:24 PM by Anand Chouhan

# Sort by Load Order - help needed

I've a table named as 'XYZ' and a column named as 'COLM' in it. I've to create a chart and use the 'COLM' as a dimension. The row values in the column 'COLM' are CX,DY,ED,BS,AE,FW but on the chart i've to sort them as AE,CX,BS,DY,ED,FW (custom sort). Can anyone please help me how can i do it?(Heard about Sort by LOAD Order but not sure how to implement it)

• ###### Sort by Load Order - help needed

Hi,

You have to use wildmatch fuction to sort in data in perticular order.

HTH

Rgds

Anand

• ###### Sort by Load Order - help needed

Hi,

In the sort properties of the chart write code to custom sort data so select

Expression -> Ascending -> and write code -> Match( COLM, 'AE','CX','BS','DY','ED','FW')

which is your sort order.

And your table is like

xyz:

LOAD * INLINE [

COLM, Value

CX,251

DY,154

ED,241

BS,286

AE,123

FW, 248

];

HTH

Rgds

Anand

• ###### Re: Sort by Load Order - help needed

Hi,

See the attached sample file

HTH

And let me know

Rgds

Anand

• ###### Sort by Load Order - help needed

Hi Anand Thanks a lot for the solution. I tried the Match() and it worked.:) But i had to write the function in each and every Chart. (Sort by Expression condition). I m thinking how to do 'Load order' so that i'll load once and just by selecting "Sort > LOAD Order > original" i'll be able to load the order of the column mentioned in the script.

Here is my Sample Code can you pls advice how it can work?

XYZ:

COL1,

COL2,

COL3

FROM

[..\Data Sources\Sample.xls]

(biff, embedded labels, table is XYZ\$);

XYZInline:

LOAD * INLINE [

COLM

AE

CX

BS

DY

ED

FW ];

drop table XYZInline;

• ###### Sort by Load Order - help needed

You're almost there.  You just need to load the inline table before the main data load so the order is already defined, then drop the inline table after the main data load.  Then, in the chart just tick Load Order (or it might be labelled Original Order - can't remember!) in the sort tab.

Hope this helps,

Jason

• ###### Sort by Load Order - help needed

Hi Jason thanks a lot. Thats what i was looking for. It is very exciting when it worked as per load order in the script.

• ###### Re: Sort by Load Order - help needed

Hi Sudeep,

The jason answer is right but i think if you need different sort orders then on that condition if you load single inline and delete it not works and if you require another sort order so on that use my code like below.

xyz:

LOAD * INLINE [

COLM, Value, Value2, Value3

CX, 19, 19, 22

DY, 22, 22, 43

ED, 32, 22, 11

BS, 11, 12, 54

AE, 11, 11, 32

FW, 32, 32, 11

];

match( COLM, 'AE','CX','BS','DY','ED','FW' ) as SortOrder,

match( COLM, 'CX','DY','FW','ED','BS','AE', ) as SortOrder2

Resident xyz;

Drop table xyz;

Rgds,

Anand

• ###### Sort by Load Order - help needed

If you are loading it from a spreadsheet with very limited rows. Make sure the order of values are in the order that you want. Once you use it you can go to sort order and get it order by Load order.

Another way is to create an inline table like the one I specified below

LOAD * INLINE [

COLM ,Sortorder

AE,1

CX,2

BS,3

DY,3

ED,4

FW,5 ];

This time chose the option Sort by an expression and write expression such as sum(Sortorder).

It should also give you the custom sort

• ###### Re: Sort by Load Order - help needed

Hi sudeep,

Thanks, I explain you are right you need to just load a sort order flied in resident table to make sort order like below code and use that sort order field to sort chart.

xyz:

LOAD * INLINE [

COLM, Value, Value2, Value3

CX, 19, 19, 22

DY, 22, 22, 43

ED, 32, 22, 11

BS, 11, 12, 54

AE, 11, 11, 32

FW, 32, 32, 11

];

Match( COLM, 'AE','CX','BS','DY','ED','FW' ) as SortOrder      //It is a load sequence for xyz table..

Resident xyz;

Drop table xyz;

it generates 1,2,3,4,5,6,7 etc sort orders

You need to use this SortOrder field to sort the chart just no need to right code again and again only use field in sort by like ways you able to make so many sort orders.

See the updated sample file.

And let me know, Thanks once again i am happy to help you.

Rgds

Anand