Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sudeepkm
Specialist III
Specialist III

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)

Thanks in advance.

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

9 Replies
its_anandrjs

Hi,

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

HTH

Rgds

Anand

its_anandrjs

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

its_anandrjs

Hi,

See the attached sample file

HTH

And let me know

Rgds

Anand

sudeepkm
Specialist III
Specialist III
Author

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?

XYZSnap.JPG

// 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;

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable

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

its_anandrjs

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

sudeepkm
Specialist III
Specialist III
Author

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.

its_anandrjs

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