Check value in multiple fields and if it's

[inguli]

Hi 

I would like to perform a check on multiple numeric fields whether it's less then 4 and then where it is, replace with a text "3 or less".

I changed Numeric fields to V_WString type and added Multi-field Formula tool with formula: "If [_CurrentField_] < '4' then "3 or less" else [_CurrentField_] endif". It changed values 17 and 12 to "3 or less" as per screenshots below.

What it's the best way to do number suppression?

I also attached yxmd file with this created data.

Many thanks

[OTrieger]

@inguli 
As it is string now, just add to the formula ToNumber([field name], number of decimals)

[JosephSerpis · Accepted solution]

Hi @inguli the multi-field tool can change the data type so you dont need a select tool and because I removed that the If logic you created worked as expected after remove the quote marks around 4.

[nagakavyasri]

@inguli As we are comparing numbers, change the data type to numbers and remove ' ' for comparison

 

[inguli]

Many thanks @JosephSerpis, this led me on the right path. I did not need to change to string type but I just did because it was not entering the text "3 or less" instead it was adding just number "3" where I expected to see "3 or less", so I added Select tool to convert to string thinking that number type fields do not accept text.

But you are right I just needed to tick checkbox "Change Output Type" to V_WString in the Multi-Field formula tool to work as expected. Lesson learned!

 

Many thanks

Inga

[inguli]

@JosephSerpis, may I check if you know, which action is done first on the Multi-Field Formula tool when I run the workflow? Does it apply the formula first and then change the data type on the selected fields, ie. in this case from numeric to V_WString? 

If that's the case what it means that if I perform calculation and changing data type in the same tool I can achieve expected result but I could not achieve that using two separate tools, ie. Multi-Field Formula tool to perform the calculation and then with, let's say, Select tool to change the data type to see the expected result.

But if Multi-Field Formula tool changes data type from Numeric to V_WString first and then performs calculation action then I cannot get how mathematical function "<" is performed on a V_WString data type field.

Thank you

Inga

[JosephSerpis]

Hi @inguli it your first point the formula is applied first and then the changes the data type . Hence why you can apply a numeric function in your If formula to check if the data is less than a certain value but you can ouput a string or text into a existing column. The multi-field tool is unique amoung Alteryx as you change the datatype of a exisiting field and update it in a single tool.