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

Checking the decimal Length

taher1992
7 - Meteor

Hi there, 

 

I am new to the community. 

 

I was asked to check the length of the number of decimals after the decimal place

 

eg. 12.2123 - would give me 4

how can i check the length of the numbers after the decimal place as if i turn it into a v_w string i sometimes loose a zero at the end

eg. 12.20 - turns to 12.2 so when i check the length it gives me one

 

7 REPLIES 7
taher1992
7 - Meteor

Hi there, 

 

I am new to the community. 

 

I was asked to check the length of the number of decimals after the decimal place

 

eg. 12.2123 - would give me 4

how can i check the length of the numbers after the decimal place as if i turn it into a v_w string i sometimes loose a zero at the end

eg. 12.20 - turns to 12.2 so when i check the length it gives me one

 

 

thanks

T

jdunkerley79
ACE Emeritus
ACE Emeritus

Far too much of a brute force way but:

LENGTH(REGEX_REPLACE(TOSTRING([Field1],12),"^-?\d+.?(\d*?)0*$","$1"))

Will get length (up to 12 dp). 

 

Basically, it converts to a string and then uses a regular expression to get just the part of the string after the decimal point and before the trailing zeros.

 

Expect a better maths based approach but that should work

MarqueeCrew
20 - Arcturus
20 - Arcturus
  1. What is the length of the string?
  2. What position is the decimal in?
  3. What is the length minus the position of the decimal?

 

12.345

 

  1. Length("12.345") = 6
  2. 1 + (FindString("12.345",'.') = 2 (zero based answer) ) = 3
  3. 6 minus 3 = 3

That's how I would approach this problem.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
taher1992
7 - Meteor

The extra issue ( just realised) some of the data does not have a decimal. so that length should be zero. i will get an error if i use the find string on those fields

MarqueeCrew
20 - Arcturus
20 - Arcturus

If you use:

 

findstring("12345",'.')

 

your result will be -1.

 

IF 
     FindString([field],".") = -1     THEN 0
ELSE
     Length([Field]) - (1 + FindString([Field],".")
ENDIF

Will this work?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
JessicaS
Alteryx Alumni (Retired)

Hi all,

 

Two duplicate streams have been merged on this post (one looks to have inadvertently gone to the 'welcome to community' section)

 

Thanks!

Jess Silveri
Manager, Technical Account Management | Alteryx
taher1992
7 - Meteor

 

just wanted to say thank you for your input

 

Taher

Labels