4 Replies Latest reply: May 15, 2014 9:53 AM by JamesH _ RSS

    New Users to Monarch

    JamesH _

      Hi

       

      I have a query which I am hoping someone will be able to help me with.

       

      I have a downloaded Trial balance from our Accounts package which I use Monarch to export to Excel.

       

      The problem I have is that the accounts package does not have fixed columns when reporting the account numbers.

       

      For example, the four possible combinations are below.

      [font="courier"]12345         (Account number only)

      12345-678     (Account Number and cost centre)

      12345-678-910 (Account Number, Cost Centre, Dept)

      12345--910    (Account Number, Dept)

       

        /font[/quote]And what I want it to look like in excel is

      [font="courier"] 

       

         A                      B            C

      Account Number       Cost Centre      Dept

      12345                (null)           (null)

      12345                678              (null)

      12345                678              910

      12345                (null)           910    

       

         /font[/quote]Sadly because of sensitivity I cannot post the actual report but the above is all the possible combinations.

       

      All I need to know is how to get the data into the right columns and I can hopefully do the rest.

       

      Can anyone help

       

      JamesH

        • New Users to Monarch
          Data Kruncher

          Hi James, and welcome to the forum.

           

          One  method to extract the data as you've described would be to start by defining the entire combination as one field, then dividing that field up into the proper fields as necessary.

           

          Let's call this combination field AcctCostDept, and define it as a character field.

           

          Now we can define calculated fields in the Table window based on this field.

           

          The formula for the Acct field is straightforward:

           

          [font="courier"]Left(,5)  /font[/quote]extracting the first 5 characters from our field.

           

          Next, the CostCentre field:

          [font="courier"]if(len()=5,"",

          if(substr(,7,1)<>"-",

          substr(,7,3),""))  /font[/quote]If the field only has an account number, the CostCentre is "", an empty string. If the 7th position in the string is a dash, there's no Cost Centre code, so again, set it to an empty string. If none of those conditions are met we want the 3 characters in positions 7, 8 and 9.

           

          Finally, the Dept field:

          [font="courier"]if(len()-len(strip(,"-"))=2,

          right(,3),"")  /font[/quote]We look at the length of the field and compare that to the length of the field without the dashes. If the difference is 2, then two dashes exist in the string, and we can use the rightmost 3 characters. Otherwise 2 dashes do not appear in the string, so we set the field to an empty string, "".

           

          Based on your sample, that should do it for you.

           

          Monarch provides many functions that enable us to perform these sorts of manipulations to get exactly what we need. When time allows, have a good look at the available functions, and spend some time reading some of the online help. You'll quickly see how Monarch allows for this type of work.

           

          Let us know if you have further questions on this.

           

          HTH,

          Kruncher

          • New Users to Monarch
            joey

            0

            • New Users to Monarch
              Data Kruncher

              Of course, Joey is correct: LSplit makes it easy.

               

              I recently made a rule for myself: no posting first thing in the morning. Now I need to stick to my own rules.   redface.gif[/img]   

              • New Users to Monarch
                JamesH _

                Hi

                 

                Thank you Joey and Data Kruncher. I did use Joey's method and it works perfectly.

                 

                Regards

                 

                JamesH   smile.gif[/img]