Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Handling Text Data

asq
6 - Meteoroid

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

 

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
jack_morgan
9 - Comet

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
20 - Arcturus
20 - Arcturus

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.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels