8 Replies Latest reply: May 15, 2014 10:02 AM by Joe Berry RSS

    Debit and Credit overlapping

    Krokie23 _

      [FONT="Courier New"]Anyone,

       

      I have this Accounting Report wherein the Debit and Credit is overlapping. 

       

      Example

      GL CODE          GL ACCT NAME          DEBIT      CREDIT

      999-000001-01 GL Account 1        1,000.00        

      999-000002-02 GL Account 2                  1,000,000.00

      999-000003-03 GL Account 3     100,000.00

      999-000004-04 GL Account 4                10,000,000.00

       

       

      How can i trap these kind of report. Please HELP!:confused:[/FONT]

        • Debit and Credit overlapping
          Krokie23 _

          [FONT="Courier New"]My example is not really a correct example.

           

          The problem is that the some portion of the Credit column, overlaps the debit column. It looks something like this (where _ is actually a space)

          ACCOUNT_____________DEBIT___CREDIT

          GL Account1_____1,000.00

          GL Account2___________1,000,000.00

          GL Account3_______900.00

          GL Account4__________10,999,999.00[/FONT]

           

           

           

          [FONT="Courier New"]Anyone,

           

          I have this Accounting Report wherein the Debit and Credit is overlapping. 

           

          Example

          GL CODE          GL ACCT NAME          DEBIT      CREDIT

          999-000001-01 GL Account 1        1,000.00        

          999-000002-02 GL Account 2                  1,000,000.00

          999-000003-03 GL Account 3     100,000.00

          999-000004-04 GL Account 4                10,000,000.00

           

           

          How can i trap these kind of report. Please HELP!:confused:[/FONT][/QUOTE]

            • Debit and Credit overlapping
              Grant Perkins

              Hi Krokie and welcome to the forum.

               

              Hmm, that's and interesting design of report. Presumably the designer had a reason ... but then again maybe not. I assume this is an ASCii report not a conversion form a pdf?

               

              There could be several approaches to this all of them very dependent on the specific physical layout as to what would work best.

               

              Assuming that the decimal points are always in the same place for each column (and not in the same place for both columns)AND that you never have both a debit and a credit on the same line AND that there is no other identifier for the line type (DR/CR) then I would be tempted to extract BOTH columns as a single CHARACTER field to start with.

               

              Then a calculated field assessing the position of th decimal point using the INSTR() function will, hopefully, tell you whether the value is a credit or debit and allow you to convert it to an appropriate numeric field.

               

              The VAL() and IF() functions will be in play for that.

               

              If you have not used the function previously check out the HELP entries as a first step. If that still leaves you unsure of how to progress  - and it might since this isort of slice and dice is not everyday stuff - let us know.

               

              HTH.

               

              Grant

                • Debit and Credit overlapping
                  Krokie23 _

                  Great!

                   

                  Thanks for that..

                    • Debit and Credit overlapping
                      Krokie23 _

                      HI,

                       

                      I've got another problem here.. this is my report:

                       

                      1.0 GROUP HEADER

                      1.1 Sub Group 1

                           DETAIL

                      1.2 Sub Group 2

                           DETAIL

                           DETAIL

                      2.0 GROUP HEADER

                      2.1 Sub Group 1

                           DETAIL

                      3.0 GROUP HEADER

                      3.1 Sub Group 1

                           DETAIL

                      3.2 Sub Group 2

                       

                       

                      How can i capture the GroupCode/Desc and SubGroupCode/Desc if they fall on the same column? Thanks..

                        • Debit and Credit overlapping
                          Grant Perkins

                          If you do need to capture both lines as appends to the detail you will need to find something on the lines that differentiates them and allows separate capture.

                           

                          Alternatively - do you really need both?

                           

                          Can a 1.0 line's information be interpreted from information on a 1.1 line for example? If so can you trap just the 1.1 type lines and create a calculated field?

                           

                          Alternatively could you use a lookup from a 1.1 type line to provide the relevant data from what I am guessing is a fairly static entry for 1.0 lines?

                           

                          Unless you have some consistent formatting to work with these may become rather data dependent traps.

                           

                          HTH.

                           

                           

                          Grant

                            • Debit and Credit overlapping
                              Joe Berry

                              If there is no way to differentiate the Group and Sub Group headings other than the .0, then you could do a model to define the types of headings based on the ID number.  Export that to a file and use a second model to parse the data from that.

                               

                              Hope that helps.

                               

                              Joe

                                • Debit and Credit overlapping
                                  Olly Bond

                                  Hi Joe,

                                   

                                  It would seem that the main headings are all N.0, as opposed to the subheadings N.1, N.2 etc.

                                   

                                  In v9, you could trap the main headings with a literal N.0 trap, and the subheadings with an N. followed by a 0 with the NOT trap highlighted.

                                   

                                  HTH,

                                   

                                  Olly

                                    • Debit and Credit overlapping
                                      Joe Berry

                                      I started using Monarch back in the DOS days and confess to doing things the hard way sometimes.  I have not used the "not" trap before.  I put your trap together in a model using a floating trap - for when the numbers get to nn.0 and nnn.0, etc. and it works great.  Much better than a two step approach.  Thanks for the tip.