Checking the decimal Length
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- What is the length of the string?
- What position is the decimal in?
- What is the length minus the position of the decimal?
12.345
- Length("12.345") = 6
- 1 + (FindString("12.345",'.') = 2 (zero based answer) ) = 3
- 6 minus 3 = 3
That's how I would approach this problem.
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all,
Two duplicate streams have been merged on this post (one looks to have inadvertently gone to the 'welcome to community' section)
Thanks!
Manager, Technical Account Management | Alteryx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
just wanted to say thank you for your input
Taher
