Add number to substring

Solved!
pnaik1
Level 3
Add number to substring

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

0 Kudos
1 Solution
AlexT
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"))

 

Screenshot 2021-06-28 at 19.08.03.png

Let me know if this was what you were looking for. 

View solution in original post

0 Kudos
3 Replies
AlexT
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"))

 

0 Kudos
pnaik1
Level 3
Author

Thanks a lot @AlexT 

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

2.PNG

Thanks!

0 Kudos
AlexT
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"))

 

Screenshot 2021-06-28 at 19.08.03.png

Let me know if this was what you were looking for. 

0 Kudos