Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
LesJean
New Contributor III

Can't figure out how to sort by expression

Hello everyone!

So I'm trying to sort the dimension of a pivot table in a very specific order and I can't seem to quite get what I need. Here's the expression I have so far:

=If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65',1,
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35',2, 
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57',4, 
If(tblArticleMaster_SectAct = 'M60',5, 
If(tblArticleMaster_SectAct = 'M40',3, 
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99',6, 
If(tblArticleMaster_SectAct = 'Other',7))))))) 

When I order by Ascending, I get the order 4-1-6-2-3-5. When I order by Descending, I get the order 5-3-4-1-6-2. What am I doing wrong here? I'm guessing my expression probably has an error..

Any help would be greatly appreciated!

Thank you,

LesJean

1 Solution

Accepted Solutions

Re: Can't figure out how to sort by expression

How about this

=Match(
If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35', 'Produits Oto-Rhino-Ophtalmiques', 
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales', 
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques', 
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires', 
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers', 
If(tblArticleMaster_SectAct = 'Other', 'Non attribué')))))))
, 'Produits parentéraux'
, 'Produits Oto-Rhino-Ophtalmiques'
, 'Suppositoires', , 'Préparations orales' , 'Préparations topiques' , 'Produits divers' , 'Non attribué')
5 Replies
hacr-sds
Contributor

Re: Can't figure out how to sort by expression

How about using the MATCH function?

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Conditiona...

If you list in your given order of your IF sentence.

match( tblArticleMaster_SectAct, 'M10', 'M11', 'M15', 'M16', 'M20', 'M25', 'M65', 'M30', 'M35', 'M40', 'M50', 'M57', 'M60', 'M00', 'M45', 'M55', 'M70', 'M99', 'Other')

Then the returning value would be lower towards the left side of the list.

 

On a side note - put a new dimension in your PIVOT, with your IF-sentence to see what's actually evaluated for each value.

 

LesJean
New Contributor III

Re: Can't figure out how to sort by expression

Thank you for your quick answer mate.

Your suggestion doesn't seem to quite work. I'll give more details as it may help resolving my situation.

Here's the code of the dimension I'm trying to sort:

=If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35','Produits Oto-Rhino-Ophtalmiques', 
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales', 
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques', 
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires', 
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers', 
If(tblArticleMaster_SectAct = 'Other', 'Non attribué'))))))) 

As you can see, I'm basically regrouping different SectAct into groups that I'm using as the dimension.

So I tried using the Match function you've given me and here's the sorting order I got:

4-1-6-2-3-4

I also tried using these variations of your formula

=Match(tblArticleMaster_SectAct, 'M10' or 'M11' or 'M15' or 'M16' or 'M20' or 'M25' or 'M65', 'M30' or 'M35', 'M40', 'M50' or 'M57', 'M60', 'M00' or 'M45' or 'M55' or 'M70' or 'M99', 'Other')
=Match(tblArticleMaster_SectAct,'Produits parentéraux', 'Produits Oto-Rhino-Ophtalmiques', 'Suppositoires', 'Préparations orales', 'Préparations topiques', 'Produits divers', 'Non attribué')

With the first one I got a sorting order of 4-1-6-5-2-3 and I also got 4-1-6-5-2-3 with the second one.

I tried your suggestion of using my original formula inside a separate dimension to check to numbers returned and I do get the correct values. Sorting numerically ascending on this new dimensions gets me the exact order I need, but I can't use this technique as I need this pivot table to look a certain way and adding an additional dimension makes the table not as aesthetically pleasing.

I hope we can figure this out.

Thanks again for your suggestions!

 

hacr-sds
Contributor

Re: Can't figure out how to sort by expression

I have a hard time to figure out your exact wish. Is it possible to see some test data or a screenshot? 

Do you have other dimensions in your SORT-properties? Maybe it's as simple as moving one above the other in order?

Re: Can't figure out how to sort by expression

How about this

=Match(
If(tblArticleMaster_SectAct = 'M10' or tblArticleMaster_SectAct = 'M11' or tblArticleMaster_SectAct = 'M15' or tblArticleMaster_SectAct = 'M16' or tblArticleMaster_SectAct = 'M20' or tblArticleMaster_SectAct = 'M25' or tblArticleMaster_SectAct = 'M65', 'Produits parentéraux',
If(tblArticleMaster_SectAct = 'M30' or tblArticleMaster_SectAct = 'M35', 'Produits Oto-Rhino-Ophtalmiques', 
If(tblArticleMaster_SectAct = 'M50' or tblArticleMaster_SectAct = 'M57', 'Préparations orales', 
If(tblArticleMaster_SectAct = 'M60', 'Préparations topiques', 
If(tblArticleMaster_SectAct = 'M40', 'Suppositoires', 
If(tblArticleMaster_SectAct = 'M00' or tblArticleMaster_SectAct = 'M45' or tblArticleMaster_SectAct = 'M55' or tblArticleMaster_SectAct = 'M70' or tblArticleMaster_SectAct = 'M99', 'Produits divers', 
If(tblArticleMaster_SectAct = 'Other', 'Non attribué')))))))
, 'Produits parentéraux'
, 'Produits Oto-Rhino-Ophtalmiques'
, 'Suppositoires', , 'Préparations orales' , 'Préparations topiques' , 'Produits divers' , 'Non attribué')
ben_skerrett
New Contributor II

Re: Can't figure out how to sort by expression

This is the solution i used when i had a similar problem