Knime - writing a Column Expression

I have a list of dates in the following format and would like to convert them to a “delta” in ()

Year(Delta)
FY21Q1(0)
FY21Q2(1)
FY21Q3(2)
FY22Q1(3)
FY22Q2(5)
FY22Q3(6)
FY22Q4(7)
FY23Q1(8)
FY23Q2(9)
FY23Q3(10)
FY23Q4(11)

Hi @AshlynHo08 , and welcome to the KNIME community.

Whilst maybe there are other people on the forum who understand what you are trying to do from your description, would it be possible for you to give some examples of your expected output for some or all of the data that you have listed. This way more people may be able to assist you.

It’s always good to give an example of required output where possible to assist with clarification of your requirement.

Edit: Additionally your title includes mention of Column Expression, which may or may not be the best node for the job. Are you specifically wanting this to be done using a Column Expression? And if so, is there a reason for this? Generally it is best to not be too prescriptive about the nodes to be used as the choice really does depend on the approach taken.

Thanks

5 Likes

I am making a lot of assumptions here, but if your data exactly matches what you posted then the easiest way to do this is a Regex Split (or use the Cell Splitter node) on “(“ to split off your delta into a new column. Then do a Regex replace that replaces “)” with blank “” on the newly separated delta column.

I could certainly help you do it all in 1 shot with a nested Column Expression node formula next time I have KNIME open if you want to see it for educational reasons, but I would go with a quick multi-step process at first.

Or you could just do a second round of the cell splitter with “)” as your deliminator instead of going with Regex replace in the column expressions node.