Extract numbers from one field to display in another field

Answered

Follow

Troy Griffin

December 05, 2017 20:39

Hello,

I currently have a field (named 'Credit') in a view that contains both text and numbers (example: 'CPE,6').

My goal is to copy the numbers from the first field and display them in another field.

So, the other field (Field Name: 'Credit Hours') would display only the '6', but not the 'CPE,'

What is the best way to accomplish this without having to revise the view? 

0

• 

  Adi Hecht December 06, 2017 07:09

  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
• 

  Troy Griffin December 07, 2017 15:47

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

  0

  Comment actions Permalink
• 

  Troy Griffin December 07, 2017 15:48

  Sorry - Thank you for your reply :)

  1

  Comment actions Permalink
• 

  Adi Hecht December 07, 2017 18:51 (Edited December 07, 2017 18:51)

  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
• 

  Evan York December 14, 2017 22:10 (Edited December 14, 2017 22:10)

  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
• 

  Troy Griffin January 08, 2018 21:29

  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
• 

  Oxana Noa Umansky January 09, 2018 17:27

  Thanks Adi, Evan and Troy.

  It takes a village! 🙌

  0

  Comment actions Permalink
• 

  Admin April 03, 2019 13:14

  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.