String manipulation with conditionals

KNIME Analytics Platform
1

Hi dears, i need to arrange the date strings to YYYY from different formats (YY, YYMMDD up to 1996 and DDMMYYYY for upper).
I´m trying to create the follow:
if the length is 6 or less, extract the firsts 2 characters and merge to “19”, else extract the right 4 characters.
I suppose it is super ease because the excel possibilities. But because I´m a ultra beginner, any help is welcome.

image
image1088×777 184 KB

1 Like
2

Hey @jeanmarlo,

I too am having problems using if() statements in String Manipulation node.

So I tried using Column Expression node

2
2610×508 23.2 KB

1
1749×705 15.4 KB

See if it works…

Regards,
Yogesh

6 Likes
3

Yogesh definitely provided the solution here - I played around with the new Expressions node though and honestly found it a bit difficult xD. Still getting used to that new Expressions language, but from what I heard in the “What’s new session” it will be definitely worth the effort with what is planned for the future…

if(length($["column1"]) <= 6,
     "19"+substring($["column1"],1,2),
     substring($["column1"], length($["column1"])-3, 4))

The above expression will also work (replacing “column1” with whatever your column name is.

image
image1718×923 123 KB

3 Likes
4

Another approach that I prefer to use is to use the rule row splitter to divide different string date formats, then convert them to date data types independently, then concatenate back together.

Then it becomes simple to use the Extract Date& Time Fields for generating sting columns for year, month, day, etc. I really like the number representation of month and day since it simplifies formulas and adjustments for things like changes to a fiscal year period.

3 Likes
5

Hello @jeanmarlo , and welcome to the KNIME Community.

The syntax in ‘String Manipulation’ differs from other nodes. In this specific use case you need a function returning Boolean in String format… the most similar to your example would be:

string(toBoolean(length($DTOBITO$) <= 6)).equals("true")
	? join("19", substr($DTOBITO$, 0, 2) )
	: substr($DTOBITO$, length($DTOBITO$)-4)

A more conventional use for the validation rule, would be the use regexMatcher()

regexMatcher($DTOBITO$, "^\\d{0,6}$").equals("True")
	? join("19", substr($DTOBITO$, 0, 2) )
	: substr($DTOBITO$, length($DTOBITO$)-4)

And finally, in pure regex() for fun:

regexMatcher($DTOBITO$, "^\\d{0,6}$").equals("True")
	? regexReplace($DTOBITO$, "(\\d{2})?.+", "19$1")
	: regexReplace($DTOBITO$, ".+(\\d{4})", "$1")

BR

5 Likes
6

Further to @gonhaddock 's post, see here for other String Manipulation “features”…

3 Likes
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.