10 Replies Latest reply: May 15, 2014 10:07 AM by Data Kruncher RSS

    Balancing Act

    Rebekah T

      I am sure that this is one of those fancy function things that SOMEBODY has figured out before, and why reinvent the wheel? Especially if math gives a person hives?

       

      This is the issue - I need to balance my report data to the total in the system, and I need to minus out all of the reversals. Sounds easy - but wait, there's more. The lines with a tax ID number (cleverly masked here) and that haven't been reversed are entered into the system - you can tell reversals because the transaction is repeated with all of the same data minus the tax ID, but with a tran code 81. There are other entries on the report that went in with no tax ID number at all, and these need to be ignored - they are not in the system that I am balaning to. I could say I want nothing with a tax ID = 0, but then I have all of the transactions that were subsequently reversed. I really need logic that states IF all these fields are the same (OTC, Teller #, Tran Amt, Int Amt) get rid of one of them. A number of the reversals are then reposted with the same data (don't ask me why) so I don't want to reverse ALL of the ones that have matching criteria - only the one. Migraine meds, anyone?

       

      Here is my example:

       

      [FONT="Courier New"]

      Tax ID       OTC          REV   BRCH  TILL         TELLER     ACCOUNT        TRANSACTION            INTEREST      EFFECTIVE         

                                TRAN  NBR   NBR          NUMBER     NUMBER         AMOUNT                 AMOUNT        DATE              

       

      (null)                      0600000111  010    106   600           1006                       1,031.36              631.36       20100121         

      (null)                      0700000094  010    107   700           4582                         100.96                 .96       20100121         

      0057#######               081    116  1608           1016                      12,640.00            7,640.00       20100121         

      0057#######   1608000066  010    116  1608           1016                      12,640.00            7,640.00       20100121         

      0057#######   1608000066  010    116  1608           1016                      12,640.00            7,640.00       20100121  /FONT

       

      Sorry - it is just not lining up right. HTML is not nice to tab delimited fields.

       

      Can you get enough out of this to see the dilemma?

       

      Help and thanks - !

      Rebekah T

        • Balancing Act
          Olly Bond

          Hello Rebekah,

           

          We'll have an easier time of it if you can edit your post to put and tags around your report (without the spaces).

           

          From what you say, we should be able to get the data you need using an advanced filter and a summary in one model.

           

          Best wishes,

           

          Olly

            • Balancing Act
              Data Kruncher

              Something like this?

               

              Tax ID  OTC  REV        BRCH    TILL   TELLER   ACCOUNT TRANSACTION   INTEREST  EFFECTIVE   

                           TRAN       NBR     NBR    NUMBER   NUMBER  AMOUNT        AMOUNT    DATE                                                                               

              (null)       0600000111 010     106      600    1006     1,031.36       631.36  20100121    

              (null)       0700000094 010     107      700    4582       100.96          .96  20100121    

              0057#######             081     116     1608    1016    12,640.00     7,640.00  20100121    

              0057#######  1608000066 010     116     1608    1016    12,640.00     7,640.00  20100121    

              0057#######  1608000066 010     116     1608    1016    12,640.00     7,640.00  20100121    

              /codeOlly's on the right track, an advanced filter is necessary here.

               

              To make it a little easier to get my head around, I built a new calculated field named "Key", to help isolate the duplicates. The formula is:

               

              TillTellerAccount+DateSerial1900(EffDate)[/code]Where EffDate is a Date field converting the original Character field EffectiveDate:

              CtoD(,"y/m/d")[/code]Now with the groundwork done, we can build a filter to isolate the unique values. Create a new filter, and skip the Formula tab and go right to the Advanced tab. Select the option to "Select rows according to their uniqueness. Check the boxes for unique rows and duplicated rows, selecting te first row only, as sorted (this is a v10 only option).

               

              Now check the Key field in the list of fields on the right side.

               

              This will give you only the first three records of your sample in the Table window.

               

              The chances of Key values matching that really aren't matching records should be pretty slim.

               

              Edit: I see that my "Key" fields are a bit different than those that you'd listed - I read too quickly - but it's the approach that's important rather than the specific fields. Adjust as necessary.

               

              HTH,

              Kruncher

                • Balancing Act
                  Olly Bond

                  Hello Kruncher,

                   

                  Nice work, although the danger of a Key field as posted is that if the fields are of type "Numeric" you'll need to use str() to handle them. If some of the character fields might include null values, then there's a danger that the whole Key field will be null, which would lead to rows which aren't truly duplicates being considered as such.

                   

                  Hello Rebekah,

                   

                  Which of the five lines in your sample do you want to keep?

                   

                  Best wishes,

                   

                  Olly

                    • Balancing Act
                      Data Kruncher

                      Thanks Olly.

                       

                      The potential nulls are easy enough to work around by creating additional calculated fields for each extracted field, ala:

                       

                      If(IsNull(),0,[ExtractedField][/CODE]

                       

                      And then using those calculated fields in the Key calculation.

                       

                      BTW, that's always a good idea when those fields will be used in summary. Nulls and summarys don't play very well together, and the combination can create some nasty misleading values. :eek:

                      • Balancing Act
                        Rebekah T

                        Hi Olly -

                        Just the last line on this one. The first 2 never made it to the system because they didn't have a tax ID and the second 2 are a post and reversal, so also aren't in the final tally.

                        Thanks so much for your response - I am going to see what I can do with what you and DK have given me - but I am not out of the woods yet ~

                        RT

                          • Balancing Act
                            Olly Bond

                            Hello Rebekah,

                             

                            Based on Kruncher's filter, if you define a calculated field called SignedAmount with the expression if(Brch="081";-1*Amount;Amount), then you should be able to get the cancelling out you want using a summary...

                             

                            Good luck,

                             

                            Olly

                        • Balancing Act
                          Rebekah T

                          Kruncher, you have a step here that requires V10, but I am only at V9 (this is a v10 only option) - can I still do this?

                          RT

                            • Balancing Act
                              Data Kruncher

                              Yes, you'll be fine. It's just the label of the option button that's different in v10, and since you're not using a custom sort order (?) then that portion of the label doesn't apply anyway.

                                • Balancing Act
                                  RalphB _

                                  I'm sure it slipped Krunchers & Ollys mind, but if you want, you do not have to use a calculated field for the Effective Date.  Under the Options on the Menu Bar, select Input and Change the Date Format: to "y/m/d" and on the Field Properties for the Effective Date, select date format and Monarch will display it in the m/d/y format.