Need help on Qlik column sorting

[praveensprabhu]

Hello Qlik Wizards,

I have a requirement to sort a column by latest year on top. After sorting it by Year need to sort the rest of the characters in alphabetical order. 

I have provided the sample data here.

Primary Name	List Price
2005 MONTREAL MAINE PRIMARY	$ 46.50
2006 BNSF AC 4400 CONTRACT	$ 50.00
2006 CERREJON PARTS CONTRACT	$ 54.66
2006 KCS US MSA PARTS CONTRACT	$ 52.84
2007 CERREJON PARTS CONTRACT	$ 56.24
2007 CN PRIMARY	$ 53.88
2007 COMILOG PRIMARY	$ 34.37
2007 KCS US MSA PARTS CONTRACT	$ 55.16
2007 ZHORGA	$ 54.14
2008 AMTRAK NONCONTRACT PARTS	$ 38.89
2008 BNSF AC 4400 CONTRACT	$ 51.85
2008 CERREJON PARTS CONTRACT	$ 68.05
2008 KCS US MSA PARTS CONTRACT	$ 57.81
2008 M&S DIST PRICE	$ 58.99
2008 MONTREAL MAINE PRIMARY	$ 57.03
2008 QTR PRIMARY	$ 32.15
2008 RAILAMERICA CONTRACT	$ 34.48
2009 BNSF AC4400 CONTRACT	$ 56.15
2009 CERREJON PARTS CONTRACT	$ 64.65
2009 COMILOG CONTRACT	$ 39.96
2009 KAZ TRANSFER PRICE LIST	$ 18.54
2009 TRANSIT	$ 65.28
2010 AMTRAK NONCONTRACT PARTS	$ 39.78
2010 BNSF AC4400 CONTRACT	$ 56.94
2010 CERREJON PARTS CONTRACT	$ 66.24
2010 KAZ PRICE LIST	$ 13.72
2010 MONTREAL MAINE PRIMARY	$ 58.34
2010 RAILAMERICA CONTRACT	$ 35.57
2010 TRANSIT	$ 68.54
2010 UGL LTPA CONTRACT	$ 46.77
2011 AMTRAK NONCONTRACT PARTS	$ 40.58
2011 AMTRAK NONCONTRACT PARTS - 2H	$ 42.20
2011 CONTRACT BNSF AC4400	$ 55.97

Can anyone help to solve this sorting issue.

Thanks,

Praveen

[Anonymous · Accepted solution]

You'll need to separate the year from the Primary Name field first, then sort Year descending and Name ascending.

One possible way is to create a Year and Name Dimension in a straight table, or directly in the script;

Left([Primary Name],4) as Year

Right([Primary Name], Len([Primary Name]) - Index([Primary Name], ' ')) as Name

[Price List]

JohnInSD

(aka johncaqc)