Add number to substring

pnaik1

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 !!!

Alexandru

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.

Alexandru

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"))

pnaik1

Thanks a lot @AlexT

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

Thanks!