topic Issue with Sort in QlikView

Issue with Sort

kennyk20 — Wed, 12 Oct 2016 18:40:33 GMT

I'm trying to sort a text field by manually assigning numeric values.

I've tried the following formula in the SORT/Expression selection within a PivotTable

if(ServiceLineCode='IC',1,if(ServiceLineCode='TDS',2,if(ServiceLineCode='FBD',3)))

then checked Numeric Value Ascending. Doesn't seem to pick up the field and sort. Any Ideas?

Re: Issue with Sort

sunny_talwar — Wed, 12 Oct 2016 19:11:59 GMT

Would you be able to share a sample where you have been trying this out to see what you have right now?

Re: Issue with Sort

sergio0592 — Wed, 12 Oct 2016 19:26:56 GMT

Maybe, sort by expression with : =index(ServiceLineCode, 'IC', 'TDS','FBD')

Re: Issue with Sort

Anonymous — Wed, 12 Oct 2016 19:27:25 GMT

May be try this?

In the sort tab...> expression...>

match(ServiceLineCode,'IC','TDS','FBD')

Re: Issue with Sort

NickHoff — Wed, 12 Oct 2016 19:31:48 GMT

if(ServiceLineCode='IC',1,if(ServiceLineCode='TDS',2,if(ServiceLineCode='FBD',3))) AS ServiceLineSort

The expression above would be in your load script, and the sort would be the field name above (ServiceLineSort) as your expression. Alternatively you could use the match expression Alluraiah provided.

Re: Issue with Sort

johnw — Wed, 12 Oct 2016 23:11:53 GMT

I generally handle this sort of sorting with an inline table at the top of the script that I drop at the end, then sorting in load order.

[Service Line Code Sort Order]:
LOAD * INLINE [
ServiceLineCode
IC
TDS
FBD
];

...rest of script...

DROP TABLE [Service Line Code Sort Order];