10 Replies Latest reply: Dec 18, 2015 9:49 AM by Peter Cammaert

# Custom sort ???

hello

i have a field model as you can see below

Model

<2
Total <2

12 i

25 k

2-3

Total 2-3
29 k
26 k
3-4
29 j
26 j
25 j
Total model
Total 3-4

i want to sort it as below in pivot table

Model

<2

12 i

Total <2

2-3

25 k
26 k
29 k
Total 2-3
3-4
25 j
26 j
29 j
Total 3-4
Total model

Thanks & Regards

Ahmar

• ###### Re: Custom sort ???

Ji Ahmar,

Try sort expression as

=Match(FieldName,'B','A','Z','Y')

• ###### Re: Custom sort ???

Sort - > Expression ->

Try like this:

=if(Model='<2',1,

if(Model='12 i',2,

if(Model='Total <2',3,

if(Model='2-3',4,

if(Model='25 K',5,

if(Model='26 K',6,                          ////and so on.......

if(Model='29 K',7)))))))

Then sort is ascending or descending

• ###### Re: Custom sort ???

Hi,

Try like this, add below script in your script

ModelSort:

LOAD

*

INLINE [

Model, Order

<2, 1

12 i, 2

Total <2, 3

2-3, 4

25 k, 5

26 k, 6

29 k, 7

Total 2-3, 8

3-4, 9

25 j, 10

26 j, 11

29 j, 12

Total 3-4, 13

Total model, 14

];

Now in Pivot table Sort Tab select give Order in Expression text box.

Hope this helps you.

Regards,

jagan.

• ###### Re: Custom sort ???

hi all

my model field is actually a combination of model,submodel and total fields.

I want to sort my data both this field in a single dimension

• ###### Re: Custom sort ???

NP, after combination try what I have suggested you...

It will work for sure

• ###### Re: Custom sort ???

hi Balraj,

Thanks for your reply.

i can use your solution but there is problem since the number of model are not fixed all the time.this would not be dynamic solution to my problem.if new model have been added i need to change the loop again

• ###### Re: Custom sort ???

Agreed.

When it comes to hardcoded sorting unfortunately no option

• ###### Re: Custom sort ???

You will always have to change some code or configuration (either externally or internally) as there is no relation between the Model value and the sort order. What place will arbitrary new Model value xyz (replace with any possible value) get? If you can describe that logic, you can program a dynamic ordering solution.

Best,

Peter

• ###### Re: Custom sort ???

Hi Ahmar,

At the beginning of you script add this:

Temp:

LOAD * INLINE [

Model

<2

Total <2

12 i

25 k

2-3

Total 2-3

29 k

26 k

3-4

29 j

26 j

25 j

Total model

Total 3-4

];

This sets the load order for the elements of the field Model. At the end of the script

DROP Table Temp;

This gets rid of the temporary table but QlikView retains the load order of the field values. Now when you want to sort in a chart set the sort order of the field Model to Load Order.

• ###### Re: Custom sort ???

Hi,

try this. You can use it with a range from I think <1 to 8-9 and 1 A to 999 z

```//SORT ORDER
//ZYXXXWWW
//Z....... number range
//.Y...... head or total flag (0 or 9)
//..XXX... int based range sort    (Must be between 0 and 999)
//.....WWW char based range sort (Ord(chr) must be between 0 and 999)
Sort:
LOAD
Model,
if(IsNum(Left(Model,1)),    //Check for number as start
if(IsNum(Mid(Model,2,1)),    //check if second char is a number
(Num#(Left(Model,1)) * 10000000) + (Num#(Left(Model, Index(Model,' '))) * 1000) + (Ord(Mid(Model, Index(Model,' ') + 1, 1))),    // Make sort int based on string like [int][int][char]
Num#(Left(Model,1)) * 10000000),    //Make sortorder based on first int
if(Left(Model, 1) = '<' ,    //check for '<' as start
(Num#(Mid(Model, 2, 1)) - 1) * 10000000,    //make sort order for <[int] ([int] - 1)
if(IsNum(Mid(Model, Index(Model,' ') + 1, 1)),    //check for number after space (like total [int]-[int])
(Num#(Mid(Model, Index(Model,' ') + 1, 1)) * 10000000) + 9000000, //make sort int based on first int after space
if(Mid(Model, Index(Model,' ') + 1, 1) = '<',    //check for '<' after space (like <[int])
(Num#(Mid(Model, Index(Model,' ') + 2, 1) - 1)* 10000000) + 9000000, //make sort int based on first int after '<'
99999999))    // sort order for last line (like total model)
)
) as ModelSort
Inline
[
Model
<2
12 i
Total <2
2-3
25 k
26 k
29 k
Total 2-3
3-4
25 j
26 j
29 j
Total 3-4
Total model
];

```

Regards

Sebastian Lettner