Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Thanks in advance.
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
Hi,
You have to use wildmatch fuction to sort in data in perticular order.
HTH
Rgds
Anand
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
Hi,
See the attached sample file
HTH
And let me know
Rgds
Anand
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?
// Loading Table XYZ
XYZ:
LOAD COLM,
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;
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
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
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
];
load *,
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
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.
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
];
load *,
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