14 Replies Latest reply: May 15, 2014 10:02 AM by JCC _ RSS

    Calculated field on a numeric currency data

    JCC _

      I have a field, TTL CHARGES, that is formatted as numeric and currency that I need to find the "$" in each field to insure that the template has captured all of the numbers. This is a sample,    $946.233900. I tried this, Left(LTrim(Str()),1), and variations of this but no luck. Thank you for assistance regarding this issue.

      Monarch Pro Version 7.0.

        • Calculated field on a numeric currency data
          Data Kruncher

          Hi JCC,

           

          Does the dollar sign appear in the report, or is it just being added by the Currency formatting of the numeric field? I believe it's the latter.

           

          Your formula won't be giving you the results you expect because the dollar sign isn't actually part of the field value; Monarch's just adding it on-screen.

          The field value itself is really only just the number.

           

          I'd recommend using the Verify feature in the report window to ensure that the model is capturing the data as you intended.

           

          HTH,

          Kruncher

            • Calculated field on a numeric currency data
              JCC _

              The raw report actually contains the "$" sign. Example;         $0.008850.

                • Calculated field on a numeric currency data
                  Data Kruncher

                  Ah, in that case you'll need to extract it as a character type field instead of a numeric field, and then the basic idea behind your original formula would work just fine.

                   

                  Left(,1)[/CODE]

                   

                  will (should - if the model's working as intended) get you the dollar sign.

                   

                  Of course you'll need another calculated field to convert it into a true number if you need to do any calculations with the values.

                    • Calculated field on a numeric currency data
                      JCC _

                      So how should the Calculated formula read? I have several different approaches with no success.

                        • Calculated field on a numeric currency data
                          Data Kruncher

                          You could build a DollarCheck field with:

                          if(left(TTL,1)="$","Pass","Fail")[/code]A filter built to isolate the "Fail" instances would help with the verification process.

                           

                          And a Currency formatted numeric TTLCharges field with nothing but:

                          val()[/code]would finish off the job.

                            • Calculated field on a numeric currency data
                              JCC _

                              So the first formula only works if the field is saved as a character field first, correct?

                              should this work?

                              if(Left(Str(),1)="$","Pass","Fail")

                                • Calculated field on a numeric currency data
                                  Data Kruncher

                                  That's right.

                                   

                                  I typed TTL when it should have been though. Rushing nearly always messes things up.

                                    • Calculated field on a numeric currency data
                                      Grant Perkins

                                      A couple of thoughts here though they may not be entirely applicable.

                                       

                                      Firstly miost reports (as opposed to csv type outputs) have a fixed max size for a field and most formatted numeric and fiscally formatted fields frmo a given system will have a known max size. Can this information be used?

                                       

                                      Secondly if you are just checking for the existence of a $ sign in a field then iirc INSTR() could be a useful function.

                                       

                                      Does this assist in any way?

                                       

                                       

                                      Grant

                                        • Calculated field on a numeric currency data
                                          JCC _

                                          Data K,

                                          Hmmm after I sent that I tried it and it works partially. All say "Fail". Not sure what is wrong with that formula? Any othere suggestions with that?

                                          Grant,

                                          This is a mainframe report so I am not sure about the thresholds of the field sizes. I haven't tried the INSTR() function yet. How would that work in this scenario?

                                            • Calculated field on a numeric currency data
                                              Data Kruncher

                                              Now might be a good time to post a little sample of your report JCC.

                                              • Calculated field on a numeric currency data
                                                Grant Perkins

                                                This is a mainframe report so I am not sure about the thresholds of the field sizes. I haven't tried the INSTR() function yet. How would that work in this scenario?[/quote]

                                                 

                                                If it is formatted (as opposed to comma separated fields or similar) it is likely to be a fixed format at print time. I guess it is just possible that the report layout varies according to the size of largest filled field but that would be very rare, in my experience, with a mainframe report or indeed almost all reports produced by a sane programming company! But I have indeed seen exceptions to such an observation ...

                                                 

                                                INSTR() would typically be used in an IF() statement but replace the LEFT() function. The check would be simply to ensure that the $ symbol exists in the string so a returned value greater >0 would give the answer. Have a look at the Help for the function - the Filter/Find example given seems to be appropriate for what I have understood you to need. (Best and easiest if it comes directly from the product oracle I feel.)

                                                 

                                                I could have misunderstood of course ...

                                                 

                                                HTH.

                                                 

                                                 

                                                Grant

                                                 

                                                PS: For DK's formula might you need to TRIM() (or a variant of TRIM) somewhere?

                                                  • Calculated field on a numeric currency data
                                                    JCC _

                                                    Ok I would like to post a sample of my report but do not see the CODE Tags referred to in the sticky post. Where are they?

                                                      • Calculated field on a numeric currency data
                                                        Data Kruncher

                                                        [URL="http://www.monarchforums.com/showpost.php?p=9903&postcount=2"]Directions for adding CODE tags icon.[/URL]

                                                         

                                                        Easier to link than re-key.

                                                          • Calculated field on a numeric currency data
                                                            JCC _

                                                            Tech  Call    Process  Settlement Serve  Home          Total                Total                Total             Total              

                                                            Type  Date     Date       Date     Sid   Sid            Kb                 Charges              Records            Bytes              

                                                            -


                                                                   

                                                              1x 08/16/08  08/19/08  09/15/08  00042 00042                    59              $0.008850                7               57,284      

                                                              1x 08/15/08  08/19/08  09/15/08  00042 30682                     1              $0.000150                1                  262      

                                                              1x 08/16/08  08/19/08  09/15/08  00042 30682                   978              $0.146700                3            1,000,339      

                                                              1x 08/15/08  08/19/08  09/15/08  00085 00085                    36              $0.005400                4               35,342      

                                                              1x 08/16/08  08/19/08  09/15/08  00085 00085                   320              $0.048000               28              311,889      

                                                            /code