1 Reply Latest reply: May 15, 2014 9:57 AM by Mike Urbonas RSS

    Trapping Non-numeric

    lcregg _

      Can anyone tell me if there is a way to trap non-numeric?  I have several lines in my report that run over one digit to the right and my model is pulling in the number from the next column.  I need to pull in the numbers with the blank and negative.

       

      Example:

      [font="courier"] 30,000

      30,000-

      30,000-1,000[/font][/quote]

       

      [size="1"][ March 29, 2005, 05:24 PM: Message edited by: Mike Urbonas ][/size]

        • Trapping Non-numeric
          Mike Urbonas

          I added the CODE option to your original post, now we can see how your numbers are not lining up.  I see what you mean now.

           

          I suggest painting a field in your model template that ensures you will capture the column of numbers you want without worrying (yet) if you capture supefluous numbers.  Specify this field as a CHARACTER field (not numeric).  Let's call this field: Original_Field.

           

          Then, in your Monarch table, try a calculated field (numeric) to cleanse the original field and show the correct number:

           

          If(instr("-",[Original_Field])>0,

          Val(LSplit(,2,"-",1))*-1,

          Val())

           

          Here is the same formula with annotations:

           

          If(instr("-",[Original_Field])>0,

          /Above checks to see if a negative sign exists/

          Val(LSplit(,2,"-",1))*-1,

          /If a negative sign exists, the field is split into two parts based on the negative sign, then converts to a value, making sure the value is negative/

          Val())

          /If no negative sign exists, just convert the original field contents from a character field to a numeric field.  The leading space is ignored./

           

          Let me know if this helps you.  If you also trap superfluous numbers when no negative sign exists, we can modify the above formula to handle that.

           

          I always recommend creating one long field in the model template and then cleaning up the results in the Monarch table view using string functions like INSTR and LSPLIT.  You can always convert the results back to a numeric value using VAL function.

           

          An excellent resource for Monarch functions is the Monarch functions guide, available on our website-definitely worth reviewing:

           

          [url="http://www.datawatch.com/pdf/products/monarch/Monarch7_Functions_Reference_Guide.pdf"]http://www.datawatch.com/pdf/products/monarch/Monarch7_Functions_Reference_Guide.pdf[/url]

           

          (Large PDF-75 pages-but very helpful.  Monarch V8 new functions to be added)

           

          Mike