Switch statement behavior

[bminuk]

I am trying to create a switch statement that replaces all content with "NA" in it to -1,  and "Tbps" to 10, and default to a number conversion 

switch([_CurrentField_],toNumber([_CurrentField_]),"NA",-1,"Tbps",1000)

Alteryx seems to report things like "ConvError: Multi-Field Formula (121): TONUMBER: NA lost information in conversion" , even though NA is one of the handled cases. NA is correctly processed to -1, however.

It seems to me that the default clause runs regardless of the success of case matching.

I want to keep error checking in place since other non numeric items may appear in the future.

[IraWatt]

Hey @bminuk,

Here's how I would do this:

Without any example data I just created my own and selected all columns and used this formula:

switch([_CurrentField_],[_CurrentField_],"NA",-1,"Tbps",10)

I didn't do toNumber([_CurrentField_]) as if the field wasn't strictly numeric text it wouldn't work.

Any questions or issues please ask :)
HTH!
Ira

[PhilipMannering · Accepted solution]

That's a good spot. It looks like, as you say, the default is calcualted in any case and then the specified mappings happen.

You could output the field to a string and then use a select tool to convert to a numerical value. That way you'd still get conversion errors when a non-numeric value appears.

[bminuk]

Thanks! It seems a touch inefficient to run the default every time, though, especially if you want to do something more extensive in case the cases fall though.

[PhilipMannering]

I'm not sure about it being less efficient. If you did specific cases before the default case you need to keep track of which records were changed. Doing the default first might mean you don't need to track which records get updated. But I'm guessing....