Clean certain string and apply calculation to the remaining number value.

Hi guys,

I’m trying to apply a calculation to multiple columns/cells, but there is some data that needs to be removed first so that I can then apply a calculation. My example looks like this:

image

As you can see some values contain “y”, meaning it is in years. The others are in days. I’ve been trying some nodes but without luck. The result should be that every value in the cells will be in days.

So my idea in logical terms was:
Identify cells with y with a regex and clean it, to then apply a multiplication of 365 days.
This way every cell is standardized. The cleaning of “y” is not a problem, but to directly apply a calculation of that particular cell I did not manage yet.

Any suggestions?

Can you share the workflow you’ve used so far to solve the problem?

I imagine (but I can’t know for sure without seeing your work) that the issue is that you’re trying to do math on a string column. After getting rid of the y’s, you’d need to convert the string columns to a number format before you can do any math.

Hello @Sjoerd,

Have you a threshold for the “day to year” calculation ? I mean, the cells to multiply by 365 are often under 10 (or another number that can be identified) ?

If so, you could do like that :
image

  • String manipulation formula : toDouble(replace($$CURRENTCOLUMN$$,“y” ,""))
  • Math Formula : if($$CURRENT_COLUMN$$ < 10,
    $$CURRENT_COLUMN$$365,$$CURRENT_COLUMN$$)

Start :
image

Result :
image
(I added Row 2 and 3 with simple multiplications to easily check/validate the results)

Br,
Samir

2 Likes

I used Python node but great to see that Knime community comes up with better solutions
ones
zero
sec

br

3 Likes

Hello @Daniel_Weikert ,

That’s great work; simple & elegant :grinning:

Br,
Samir

1 Like

Your solution as well Samir so thanks for sharing your solution here.
br and take care

1 Like

Perfect, good solution! Thanks!

2 Likes

Hi,

and you have the choice, Python or “node based workflow”, lucky guy =).
Happy to help you.

Br,
Samir

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