Extract numbers from one field to display in another field

Answered

Comments

8 comments

  • Avatar
    Adi Hecht

    Just create a custom field named "Credit Hours" in your elasticube... you can use a formula such as:

    TOINT(RIGHT(LENGTH(Credit)-indexof(Credit,','1)))

     

    I hope i got this right, not really in front of the system right now.

    1
    Comment actions Permalink
  • Avatar
    Troy Griffin

    that is close, but I get a message stating that the Function is not defined....

    0
    Comment actions Permalink
  • Avatar
    Troy Griffin

    Sorry - Thank you for your reply :)

    1
    Comment actions Permalink
  • Avatar
    Adi Hecht (Edited )

    The message would typically let you know which function is undefined, by stating the line and column in which there was an error. That could help you find where the syntax error is.

    Anyways, I can see I was missing a comma  and also the string parameter in the SQL code I suggested...

    Try this:

    TOINT(RIGHT(Credit,LENGTH(Credit)-indexof(Credit,',',1)))
    0
    Comment actions Permalink
  • Avatar
    Evan York (Edited )

    There's a nice function in Elasticube called StrParts that might be simpler.  Just make Credit Hours this:

    StrParts([Credit],',',2)
     

     And just change the 2 to a 1 for the "CPE" piece.

    0
    Comment actions Permalink
  • Avatar
    Troy Griffin

    Thanks to you both - it was actually a combination of your responses that ended up resolving the dilemma; I ended up using the following (please suggest a cleaner solution if you have one):

     

    TOFLOAT (StrParts([Credit],' ',5))

     

    this extracts just the number from the column and converts to FLOAT, now allowing the SUM of the numbers.

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Oxana Noa Umansky

    Thanks Adi, Evan and Troy.

    It takes a village! 🙌

    0
    Comment actions Permalink
  • Avatar
    Admin

    Hi

    is there a way to extract a numeric value from a string when there is no simple pattern? I mean, I have a field: Quantity and users put there things like '400kg' or '400 kg' or '0.4t' or '0.4 t' or '0.3 tonnes per year' or '670 boxes' or '50 litres' ?

    Thanks,

     

    Pawel

    0
    Comment actions Permalink

Please sign in to leave a comment.