3 Replies Latest reply on Feb 6, 2014 9:47 AM by Matt Lutton

    Combine multiple functions in order in a single calculated field


      Here's my challenge.


      I have a ton of data points (item_name)with names like this:  Disc $3 GIFT         3.00-


      The first part can come in multiple lengths. The last part is alway a "-" followed by what should be a dollar value. The dollar values go up to $10.00 as a maximum.


      I want to pull out this dollar amount from this string and am doing this via calculated fields. That is, I'm using two in order to get the final result like so:


      First Calculated Field is "Discount Trim":


      IF CONTAINS ([item_name], "Disc") //only want to apply to certain data points

      THEN REPLACE ([item_name], "-" "") //this trims off the "-" at the end.



      Then I had to do a second calculated field that uses the results of the first (Discount Trim) called: "Discount Amount" like so:


      FLOAT (RIGHT([Discount Trim], 5)) // I found the float was necessary to convert to number from string. using 5 for the RIGHT so I make sure I can get all of the number if the value is >9.99


      These two in tandem work fine and I get the numbers I want. My question is simple: how would you combine these into a single calculated field to make it a little more elegant and simple? Can you do a calculated field that basically does several functions in succession like I'm talking about here? I'm assuming there's some nesting I can do, but I can't figure it out.


      Also, if you have an alternative, simple way to accomplish the same thing that I'm doing, I'd love to hear it.


      Note that it is not important that the value be a negative number (since it's a discount).