Hello everyone, I hope you fine all.
I have an simple case and I hope we all together solve it.
I have a column with string and some of columns have a numbers and another not.
I want this string with no digit return 1 , and string with numbers return the exist number.
Thanks in advance.
Hi @Analyst_1 ,
I wasn’t sure from your description if for those rows containing numbers, the number was embedded inside strings (e.g. “abc123def”), or was just a pure numeric. I have assumed it is potentially embedded as a single sequence of digits, and have used the following sample data
Here are two ways that spring to mind, both using regex.
The first approach uses a Regex Spli
t with the following pattern
.*?([0-9]+).*?
This will return a single sequence of digits embedded in a string, and if not found returns a missing value.
After that, missing values can be turned to 1.
The second approach does the job with a single String Manipulation
call,
This uses a regexReplace
call to return the value of the column with all non-numerics replaced with empty strings. If the result is an empty string, it returns “1” but otherwise returns the value of the regexReplace
.
Extract Number or return if not not present.knwf (13.3 KB)
Of course if your numbers are not integers we may need to do something different!
I would to thank you… your workflow is really helpful!!
but what about If I want to return 1 for no digit string, and another numeric string I want it returned as a missing values ?
Hi @Analyst_1 ,
Are you saying that you have strings. And if a string contains no number then you want to set it to 1, but if it contains a number then for some numbers you want to return missing value?
You could achieve this by adding a Rule Engin
e after the previous nodes. If none of the Rule Engine
’s rules are satisfied, it returns missing value
.
e.g. to have it return missing value if the value is 100, we tell it to return a value only if the value IS 100,
Extract Number or return if not not present 2.knwf (16.6 KB)
I hope that is what you meant, but if not please can you give an example data and expected output
Or if you meant that a string containing NO numerics should return “1” but ANY string containing numerics should return missing, you could do this directly from the same data using a Rule Engine:
NOT $column1$ MATCHES ".*?[0-9]+.*?" => "1"
Thank you @takbb
I really appreciate your help, It works!!
Thank you so much I am a student and you really help me.
You could also try toNull function inside string manipulation of @takbb s great solution to convert to missing.
Thanks a lot for reminding me to that “? :” syntax in string manipulation. I completely forgot
br
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.