Sort by expression Issue

[Anonymous]

Hi  all,

In a pivot table, I wanted to sort InventoryType in this orders:'LATE ORDER','OK ORDER','FUTURE ORDER','CLOSED ORDER','NOT CLIENT','UNIT REJECTED',null()

For that I'm using this expression

Match(InventoryType,'LATE ORDER','OK ORDER','FUTURE ORDER','CLOSED ORDER','NOT CLIENT','UNIT REJECTED',null())

But, as you can see in the image attached, the line for null Inventory Type is the first one and not the last one. I've tried other things like

Match(InventoryType,'LATE ORDER','OK ORDER','FUTURE ORDER','CLOSED ORDER','NOT CLIENT','UNIT REJECTED','')

or

Match(InventoryType,'LATE ORDER','OK ORDER','FUTURE ORDER','CLOSED ORDER','NOT CLIENT','UNIT REJECTED','-')

But anything works…

Does anyone know what i might be doing wrong?

Thank you!

[sunny_talwar]

How about this

Match(InventoryType&'-','LATE ORDER-','OK ORDER-','FUTURE ORDER-','CLOSED ORDER-','NOT CLIENT-','UNIT REJECTED-','-')

[Anonymous]

Thank you Sunny, but it didn't worked...

[ychaitanya]

Can you try this.

WildMatch(InventoryType,'*LATE ORDER*','*OK ORDER*','*FUTURE ORDER*','*CLOSED ORDER*','*NOT CLIENT*','*UNIT REJECTED*')

OR

PICK(WildMatch(InventoryType,'*LATE ORDER*','*OK ORDER*','*FUTURE ORDER*','*CLOSED ORDER*','*NOT CLIENT*','*UNIT REJECTED*'),1,2,3,4,5,6)

[Anonymous]

Thank you Chaitanya,

The issue is to sort the empty InventoryType values to the end of the list. With this expressions we are not considering them, are we? I've tried your expressions and the output is the same as with mine.

[ychaitanya · Accepted solution]

Can we try below :

We are forcefully pushing the unmapped category to the end.

if(WildMatch(InventoryType,'*LATE ORDER*','*OK ORDER*','*FUTURE ORDER*','*CLOSED ORDER*','*NOT CLIENT*','*UNIT REJECTED*'),

PICK(WildMatch(InventoryType,'*LATE ORDER*','*OK ORDER*','*FUTURE ORDER*','*CLOSED ORDER*','*NOT CLIENT*','*UNIT REJECTED*'),1,2,3,4,5,6),

7)

[Anonymous]

Thank you Chaitanya! That works!

[Anonymous]

I know, but I'm not allowed to do create new fields in the script...