topic Re: Custom sort ??? in QlikView

Custom sort ???

ahmar811 — Wed, 16 Dec 2015 05:21:32 GMT

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 ???

tamilarasu — Wed, 16 Dec 2015 05:28:28 GMT

Ji Ahmar,

Try sort expression as

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

Re: Custom sort ???

Anonymous — Wed, 16 Dec 2015 05:29:06 GMT

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 ???

jagan — Wed, 16 Dec 2015 05:29:20 GMT

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 ???

ahmar811 — Wed, 16 Dec 2015 05:35:32 GMT

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 ???

Anonymous — Wed, 16 Dec 2015 05:40:15 GMT

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

It will work for sure

Re: Custom sort ???

ahmar811 — Wed, 16 Dec 2015 05:53:17 GMT

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 ???

Anonymous — Wed, 16 Dec 2015 05:55:59 GMT

Agreed.

When it comes to hardcoded sorting unfortunately no option

Re: Custom sort ???

effinty2112 — Wed, 16 Dec 2015 08:29:47 GMT

Hi Ahmar,

At the beginning of you script add this:

Temp:

LOAD * INLINE [

Model

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 ???

sebastianlettner — Fri, 18 Dec 2015 13:33:00 GMT

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

Re: Custom sort ???

Peter_Cammaert — Fri, 18 Dec 2015 14:49:54 GMT

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