Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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 !!!
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.
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"))
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.