Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Ji Ahmar,
Try sort expression as
=Match(FieldName,'B','A','Z','Y')
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
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.
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
NP, after combination try what I have suggested you...
It will work for sure
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
Agreed.
When it comes to hardcoded sorting unfortunately no option
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.
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