Discover the winners & finalists of the 2022 Dataiku Frontrunner Awards!

Solved! Level 3

So I am banging my head around and couldn't find a way to add a number to substring (formula in Prepare recipe):

``if(substring(Season, 2, 3) == 1, concat("20", substring(Season, 0, 2), "07"), concat("20", substring(Season, 0, 2) + 1, "01"))``

So in else part I want to add 1 to substring(Season, 0, 2) which is number however upper code is appending one rather adding it. So a value like 193 should be converted to 202001, however with above formula it is converting to 2019101.

Thanks !!!

1 Solution Dataiker

Hi,

Thanks for trying,  upon further testing numval only works when reading a column.

toNumber should however work in this case :

``````if(substring(Season, 2, 3) == 1, concat("20", substring(Season, 0, 2), "07"), concat(20, toNumber(substring(Season, 0, 2)) + 1, "01"))
`````` Let me know if this was what you were looking for.

3 Replies Dataiker

Hi,

Since you are doing +1 this to what is a string it will append,  if you want to actually add +1  then you would need to use numval to read it as a number.

Can you try , and see if that works?

``````if(substring(Season, 2, 3) == 1, concat("20", substring(Season, 0, 2), "07"), concat("20", numval(substring(Season, 0, 2)) + 1, "01"))
`````` Level 3
Author

Thanks a lot @AlexT

I tried but Giving error "ExpressionError: Cannot parse to number: null" Thanks! Dataiker

Hi,

Thanks for trying,  upon further testing numval only works when reading a column.

toNumber should however work in this case :

``````if(substring(Season, 2, 3) == 1, concat("20", substring(Season, 0, 2), "07"), concat(20, toNumber(substring(Season, 0, 2)) + 1, "01"))
`````` Let me know if this was what you were looking for. 