Add number to substring

pnaik1
pnaik1 Registered Posts: 23 ✭✭✭✭
edited July 16 in Using Dataiku

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

Best Answer

  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,225 Dataiker
    edited July 17 Answer ✓

    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.

Answers

  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,225 Dataiker
    edited July 17

    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
    pnaik1 Registered Posts: 23 ✭✭✭✭

    Thanks a lot @AlexT

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

    2.PNG

    Thanks!

Setup Info
    Tags
      Help me…