0 Replies Latest reply: Sep 21, 2009 10:29 AM by ironchef97 _ RSS

    converting <> to ()?

    ironchef97 _

      Hi, I am just starting to use Monarch v9, and I need to figure out a way to convert the "<>" characters into "()".  Is there any way to do that?  I'm converting text files from Avante, and the negative numbers are in this format <3992.43> for example.  But when switching to table format, the numbers just lose the <>, and become positive numbers. 

       

      Thanks!

        • converting &lt;&gt; to ()?
          Data Kruncher

          Hello ironchef97 and welcome.

           

          Monarch cannot recognize angle brackets as negative number indicators, so my solution is to trap the right angle bracket > as a Character field, and then in the Table window, create a calculated field to determine the "real" value of the number in question.

           

          For instance, if Profit was the field on the report, I'd first create the field in the template as PreProfit, and the single character field for NegProfit.

           

          Then the formula for "Profit" in the Table window becomes:

          If(NegProfit=">",-PreProfit,Profit)[/CODE]

           

          It's a bit of a pain, especially when you have many potential negative value fields, but there's no other way.

           

          If you're lucky the report has been programmed with the < and > characters in the same column. That is they don't float with numbers so as to be up against them. I've got a few of those too.

           

          If the < isn't in a fixed position as does float then you must capture the entire left side up to > as a character field, and then extract the value from the string that begins with <. The Strip function combined with a Val function returns the real value as a number for you. Again, painful, but it works.

           

          I know, I've made plenty of models with both of these calculations.

           

          HTH,

          Kruncher

            • converting &lt;&gt; to ()?
              Grant Perkins

              I would go with Kruncher's approach for full control and visibility but IF you can be sure that < and > are only ever used in the source files to identify negative values you could consider running a converison on the file before loading into Monarch and swapping < to ( and > to ).

               

              You could use a text editor for the changes or there are some (free) dedicated programs for making such substitutions that work rather well. Or there is the Monarch Utility that also offers such capability. In most cases it would be possible to create a batch file so that a single click could be used to open the source file, apply the substitutions and then open the file in Monarch or run a Monarch project file all the way through to any exports required.

               

              Somewhere in there should be a suitable solution for your needs.

               

              HTH.

               

               

              Grant

                • converting &lt;&gt; to ()?
                  Olly Bond

                  Hello ironchef97,

                   

                  Grant and Kruncher are absolutely right on how to treat your data, but I do remember that the developers of Monarch are always interested in hearing about new varieties of number, date and currency formats so that new versions of Monarch can be set to recognise these automatically.

                   

                  There's a sticky thread at the top of the forum where we could add this - I must confess I haven't worked out whether this bulletin board lets us cross post to multiple threads or not.

                   

                  Best wishes,

                   

                  Olly

                    • converting &lt;&gt; to ()?
                      Nigel Winton

                      Hello

                      As a long time Avante user I can sympathise with you.

                      I convert these by taking the whole field as a character field named credits and then use the following calculated field in the table. Same as Kruncher but with only one field on the report.

                      Fortunately Avante right aligns its number fields and the <> follow the numbers, so they are always on the end. As the left < will not always be the first character in the field, spaces will appear, I use.......

                       

                      IF(RIGHT(credits,1)=">",-VAL(credits),VAL(credits))

                       

                      If you need any more help with Avante reports and their little quirks just let me know.

                       

                      Regards

                       

                      Nigel

                        • converting &lt;&gt; to ()?
                          ironchef97 _

                          Hey guys, thanks for all the quick responses!  I will let you know how it goes once I can implement these changes into Monarch!

                            • converting &lt;&gt; to ()?
                              ironchef97 _

                              Ok so I tried to make 2 separate columns like you said and here is what is showing up currently in my table:

                               

                              Ending Balance ..........PreEnding Balance

                              5514310.67 ......................>

                              5514310.67     

                              0.00     

                              0.00     

                              202500.00     

                              0.00     

                              6686783.80 ......................>

                               

                               

                              The two values with 'PreEnding Balance' = > are supposed to be negative numbers.  Also, the long string of periods is just because the format I was trying to display wans't showing up properly using a bunch of spaces.  Ending Balance is supposed to be its own column, as is PreEnding Balance.

                               

                              This is the code I am trying to use, but it isn't working. 

                              IF(RIGHT(,1)=">",-VAL(),VAL())

                               

                              I know I am doing something wrong here, but I am not sure what, thanks for the help in advance!!

                                • converting &lt;&gt; to ()?
                                  Nigel Winton

                                  Hi there

                                  I think you are confusing the 2 methods.

                                  Question. Does your Avante report have the <> brackets next to the numbers as <123.45>. If so then my method of using only one column and making it the entire field including the <> brackets, in the report and setting the width to as large as it needs to be for the largest number will work with the

                                  IF(RIGHT(credits,1)=">",-VAL(credits),VAL(credits))

                                  formula, where credits is the name of your field.

                                  This is an example from one of my reports, the field is highlighted to the extent of the largest number and the <> brackets if they extend further and made a character field, then the calculated field in the table uses the formula to convert it to a number field with the negatives as required.

                                   

                                     Credits

                                      <705,420.45>

                                       705,420.45

                                       124,284.51

                                       153,298.54

                                     1,499,810.42

                                  /code

                                   

                                  However if the <> are at the ends of the fields as < .......123.45......>, I have had to use the dots for spacing, then selecting the > as a seperate column will work better, as in Krunchers method.

                                  The UK version of Avante always puts the <>  as in <123.45>, which does make life easier.

                                   

                                   

                                  Regards

                                   

                                  Nigel

                                  • converting &lt;&gt; to ()?
                                    Olly Bond

                                    Hello ironchef,

                                     

                                    Assume that your original field in the report is called "credits", is a character type field, and some of the entries in it have > as the right hand character.

                                     

                                    You can define one field exactly as Nigel described:

                                     

                                    Balance:

                                    IF(RIGHT(credits,1)=">",-VAL(credits),VAL(credits))

                                     

                                    If you want to have one field with positive values in and another with the negative values, you'll need two calculated fields.

                                     

                                    Balance1:

                                    IF(RIGHT(credits,1)=">",-VAL(credits),0)

                                     

                                    Balance2:

                                    IF(RIGHT(credits,1)<>">",VAL(credits),0)

                                     

                                    HTH

                                     

                                    Olly

                                      • converting &lt;&gt; to ()?
                                        ironchef97 _

                                        Yea, my version of Avante always puts the <> brackets next to the numbers like you wrote before. 

                                        This is the equation I am using now, but I am getting an error, which says "Operand Types" and the cursor goes to the area right inbetween the two bolded characters in this code:  IF[B](R[/B]IGHT(,1)=">",-VAL(),VAL())

                                          • converting &lt;&gt; to ()?
                                            Data Kruncher

                                            Chef, make sure that your Ending Balance field is a Character field, and that the calculated field that you're building with this expression is a Numeric field.

                                              • converting &lt;&gt; to ()?
                                                ironchef97 _

                                                Yay it works!!!!  Thank you guy so much!!!!  I'm sure I will have many more questions in the future, but I appreciate all the help you've given me.

                                                  • converting &lt;&gt; to ()?
                                                    Data Kruncher

                                                    Excellent. Thanks for the update.

                                                     

                                                    You might find these pieces on [URL is no longer valid]and [URL is no longer valid] interesting.

                                                      • converting &lt;&gt; to ()?
                                                        ironchef97 _

                                                        Ahh, actually I am not out of the woods yet, I was able to convert 3 of the 4 columns with negative values, but the 4th contains the first step I tried.  That was to take the > character and make them into a separate character column.  Do you know how to undo that step and recombine it with it's original column?

                                                          • converting &lt;&gt; to ()?
                                                            Data Kruncher

                                                            You'll have to edit the template to define the fields as you really want them.

                                                              • converting &lt;&gt; to ()?
                                                                ironchef97 _

                                                                Oh yea...doh.  I completely forgot that I did that earlier...Thanks!

                                                                  • converting &lt;&gt; to ()?
                                                                    ironchef97 _

                                                                    Ok, I read those links you provided, I do have a new question now.  Suppose I have 2 columns of numbers, one being Opening Balance, the other being Amount.  What kind of formula/function should I use to subtract the Amount from the Opening Balance, and output the result into a new column? 

                                                                     

                                                                    For example:

                                                                    Turn this...

                                                                    Opening Balance     Amount

                                                                    1,000.00                100.00

                                                                    1,000.00                10.00

                                                                    1,000.00                10.00

                                                                    1,000.00                25.00

                                                                    1,000.00                5.00

                                                                     

                                                                    into this...

                                                                     

                                                                    Opening Balance     Amount

                                                                    1,000.00                100.00

                                                                    900.00                   10.00

                                                                    890.00                   10.00

                                                                    865.00                   25.00

                                                                    860.00                   5.00

                                                                      • converting &lt;&gt; to ()?
                                                                        Data Kruncher

                                                                        That isn't the sort of calculation that Monarch can do easily if at all, as it doesn't share values from row to row, like Excel does for instance.

                                                                         

                                                                        Posts here on the forum that involve that sort of thing always employ exporting values using a key field, then importing those values into a separate model with an external lookup. Whether this is a possible solution for you really depends on the remainder of the original report.

                                                                         

                                                                        Others may chime in on this in due time.

                                                                          • converting &lt;&gt; to ()?
                                                                            Olly Bond

                                                                            Hello ironchef,

                                                                             

                                                                            You might get away here with a run time parameter for you to enter the opening balance by hand, then a tweaked cumulative subtotal measure in a summary.

                                                                             

                                                                            But with a two (or more) pass in Monarch, almost anything is possible. How automatic do you need it to be?

                                                                             

                                                                            Best wishes,

                                                                             

                                                                            Olly

                                                                              • converting &lt;&gt; to ()?
                                                                                ironchef97 _

                                                                                As automatic as possible..the more it can do, the less work we will have to do in excel.

                                                                                  • converting &lt;&gt; to ()?
                                                                                    Data Kruncher

                                                                                    Maybe I'm missing something, but right now I only see that this can be accomplished by performing (n-1) passes with Monarch, where n is the number of transactions captured. U-g-l-y.

                                                                                     

                                                                                    As much as I love working with Monarch, isn't this a case of the right tool for the job?

                                                                                     

                                                                                    To make this an easy calculation in Excel, I'd first setup a Monarch project export to be able to create an Excel file with the same name every time it's exported.

                                                                                     

                                                                                    Then in a new Excel workbook, I'd write a macro to open that file, and add the appropriate heading(s) and calculations to that file. I'd also use the proper workbook event macro so that the macro that adds to the Monarch export executed automatically as soon as I opened the inital Excel file. To make my life easier, if this were a regular occurence, I'd put a shortcut to the Excel file, or the file itself, on my desktop. The whole Excel update can't take more than a few seconds.

                                                                                      • converting &lt;&gt; to ()?
                                                                                        Olly Bond

                                                                                        Hello Kruncher,

                                                                                         

                                                                                        (UPDATE: single model & project emailed - summary measure of cum(debits) give required field for an external lookup.)

                                                                                         

                                                                                        I'm pretty sure you'd never need more than three passes on a job like this. Exporting the file with a calculated field of Recno() as a fixed width text will give you:

                                                                                         

                                                                                        1 - 1000 - 100

                                                                                        2 - 1000 - 10

                                                                                        3 - 1000 - 150

                                                                                        etc

                                                                                        /CODE

                                                                                         

                                                                                        There's a few approaches we could take from there - the summary and cumulative subtotals, textline(Recno()) on a multiline field. I'm not in front of Monarch right now but will have a bash at it later and probably email you some models tomorrow.

                                                                                         

                                                                                        #ironchef - if you can PM me your email address or drop a line to olly(at)greenbar.info I'd be glad to copy them to you.

                                                                                         

                                                                                        Best wishes,

                                                                                         

                                                                                        Olly

                                                                                        Always searching for new ways to eliminate Excel and VB from the desktop