Handling Text Data

[asq]

My need is two parts which is indicated below by bullets

The data coming through the workflow for this column I'm working with is text from the database and can have just about anything in it.

My goal is to only handle values that are whole numbers including decimals, excluding negative numbers. Anything outside of this including negative numbers can be ignored and pass through as a blank or [null].

I've already trimmed spaces from the front of the data

• I need a formula that will restrict the data passed through to only numbers that look like the following (95.1, 95, 101, 101.95, 0.5, 101.155)

My end goal will be to round the number up or down depending on standard evaluation of the tenths place so that the number is no longer a decimal

 (example: 101.155 will return 101, 95.4 will return 95, 95.5 will turn 96)

• Which tool and formula will handle this rounding best

[MarqueeCrew]

1.  Remove commas (if they are present, they will cause errors in your process)

2. Convert to number and ignore errors/warnings

3. Round

Round(ToNumber(replace([value],",",''),1), 1)

Use the formula tool with the statement above (change [value] to your incoming variable.

Happy Easter,

Mark

[jack_morgan · Accepted solution]

Hey asq!

Lots of ways to do this. By using a select tool to change your field type from string to double, it'll keep ANY numeric value (negatives inluded). From there you can round your data based on the tenths spot using a simple function (round([data field],n) where n is the nearest multiple. I've attached a workflow so you can see the process. Also, there are rounds, cieling and floors in alteryx so i ran all three so you can see the effect of each. Lastly, I did do that rounding clac in an if statement to evaluate any negative values first, that way they get "nulled" in the process as well.

Hope this helps!!

Jack

[MarqueeCrew]

Sorry, I forgot about the negative numbers:

IIF(Round(ToNumber(replace([value],",",''),1), 1) >=0, Round(ToNumber(replace([value],",",''),1), 1) ,null())

That will put NULL values for negative.