Issue with Sort

[kennyk20]

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?

[sunny_talwar]

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

[sergio0592]

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

[Anonymous]

May be try this?

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

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

[NickHoff]

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.

[johnw]

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];