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:
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.
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.
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 :
String manipulation formula : toDouble(replace($$CURRENTCOLUMN$$,“y” ,""))
Math Formula : if($$CURRENT_COLUMN$$ < 10, $$CURRENT_COLUMN$$365,$$CURRENT_COLUMN$$)
Start :
Result :
(I added Row 2 and 3 with simple multiplications to easily check/validate the results)
