Custom sort ???

[ahmar811]

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

[tamilarasu]

Ji Ahmar,

Try sort expression as

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

[Anonymous]

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

[jagan]

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.

[ahmar811]

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

[Anonymous]

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

It will work for sure

[ahmar811]

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

[Anonymous]

Agreed.

When it comes to hardcoded sorting unfortunately no option

[effinty2112]

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.

[sebastianlettner]

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