4 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Calculated Field help

    Dubrey _

      I need to pull together two seperate fields they are both numeric - the first is a policy number and the second is a dollar amount.

       

      Pol num  Dollar Amt

      186889    5000

       

      What I want it to look like

      1868895000

       

      I need this for an external lookup to match a join code I have created in excel - Any help would be appreciated

        • Calculated Field help
          joey

          There is uncertainty that you need to address, and make sure you handle these the same way in Excel for your lookup.  Athese all a fixed length, or are leading zeros dropped? 

           

          If every policy number is 7 positions and the amount is always 4 positions, then I would just handle them as character fields (in the template) and concatenate them with the + operator.

           

          If you need leading zeros droped, then handle them as numeric in the template, and in the calculated field, convert them to a character field (str function),  then concatenate them.

           

          Then, you need to determine if you need a string or a numeric field to link back to your spreadsheet.  Since you used character for your Monarch fields, a string will work as is.  To use numeric, use the Val function.

          • Calculated Field help
            Dubrey _

            In monarch I have used the Val(Polnum) calculated field to get rid of the leading zero's, the problem is I need the "type" of both to be numeric because I need to find the total in the summary, If I change to character I can't do that...

             

            In excel my format is "number" to 2 decimal places here is a better example of what I need to do

             

            Here is what it looks like in Monarch

            Pol num   Dolar amt

            18998    1,237.55

            167632   10,000.00

             

            What I did in excel to combine them - I need monarch to do the same

            189981237.55

            16763210000

             

            Thanks for all the help so far....

            • Calculated Field help
              joey

              I think you're asking for trouble by linking your external lookup on the combined field.  The decimal places are another complexity to tackle.  Do you have the two separate fields in your Excel spreadsheet?  Can you link the spreadsheet back in on the two numeric fields?

              • Calculated Field help
                Grant Perkins

                Originally posted by Dubrey:

                What I did in excel to combine them - I need monarch to do the same

                189981237.55

                16763210000

                /b[/quote]I'm with Joey on this and would observe that the Excel combination by dropping the decimal places ONLY if the values are zero does not make things any easier at all! (I assume that is how the combination  is working as shown above). I'm not even sure that you could guarantee a unique string although to be fair the chances of duplication would likely be extremely small.

                 

                I think you may need to include the decimal place values (and hope you don't get recurring amounts in the same PolNum if you are looking for unique links).

                 

                In theory the PolNum is likely to be a Character field in essence rather than numeric so the link should be based on character fields in my opinion. However to get the linke, if both field start as numeric, you could simply multiply the numeric value of the PolNum field by 10 to the power of the number of whole value characters in the Amount field and than add the amount.

                [font="courier"]Pol num Dolar amt

                18998 1,237.55

                167632 10,000.00[/font][/quote]1237.55 would require 4 zeros so multiply 18998 by 10,000 to get 189980000.00 . The add 1237.55.

                 

                Result 189981237.55

                 

                 

                10000.00 would require 5 zeros so multiply 167632 by 100,000 to get 16763200000 and then add 10000.00.

                 

                Result  16763210000.00

                 

                Then convert to be a character field.

                 

                There are variations to the this approach of course but in principle what ever you need needs to be simple to maintain and full proof against the vagaries of numbers in databases.

                 

                HTH.

                 

                 

                Grant