8 Replies Latest reply: May 15, 2014 10:05 AM by adonis _ RSS

    Help with Matching up records records

    adonis _

      Hi,

      I am trying to pair off records to only have exceptions where the records do not match.  The criteria is based on account # and then by security #.

      What happens in some cases One Positive () Number can be comprised of potentially 1 or multiple records with the same Account # & Security # which if added together equal the same () but in reverse with the value.  In some cases if the numbers do not match and there is a threshold for $25.00 for Tran Total Sign Amount (which would mean if one item was ie:

      Security Number, Settlement Date, Account Number, Share Trans Sign Qty, Tran Total Sign Amount

      5BFZRG7     09/14/2009     12345678     2250000     2,748,645.00

      5BFZRG7     09/14/2009     12345678     -2250000     (2,748,645.88)

      what would happen is the 2 numbers would be subtracted and the difference  (-.88) would be added into a misc field total which would be done for all items that match up like this as the user does not want to see the record but only the amount that is left over as that would be written off if it was under 25.01.   The quantities need to be the same in order to added them up.

       

      All items that do not have multiple lines/records, would show up.

       

      I am thinking merely to do a Sum by security and if it is less then or = 25 then add the data to the bottom of the data in a field called misc.  However those records would be excluded. 

      Also If the data = 0 then the data would be excluded and nothing would be added.  I am not sure how to exclude these records.

       

      Here is some example of data that shows you what I am looking at.

       

      Security Number,     Settlement Date,     Account Number,     Share Trans Sign Qty,     Tran Total Sign Amount

      3830631     09/14/2009     12345678     -65000     (71,147.12)

      3835059     09/14/2009     12345678     10000     10,493.06

      3938318     09/14/2009     12345678     -300000     (300,015.29)

      5226539     09/14/2009     12345678     10000     9,625.07

      5553959     09/14/2009     12345678     6000     5,546.15

      5588452     09/14/2009     12345678     -20000     (20,888.83)

      5907429     09/14/2009     12345678     65000     58,327.76

      5BBWQR2     09/14/2009     12345678     20000     20,160.02

      5BDHFW5     09/14/2009     12345678     4500000     4,727,172.50

      5BDHFW5     09/14/2009     12345678     -4500000     (4,727,172.50)

      5BFDVH1     09/14/2009     12345678     5000     5,421.44

      5BFDVH1     09/14/2009     12345678     -5000     (5,421.44)

      5BFJWQ9     09/14/2009     12345678     -20000     (23,338.00)

      5BFLGR2     09/14/2009     12345678     -5000     (5,316.57)

      5BFTDD5     09/14/2009     12345678     -75000     (77,538.72)

      5BFWRC4     09/14/2009     12345678     -8000     (8,104.00)

      5BFZRG7     09/14/2009     12345678     2250000     2,748,645.00

      5BFZRG7     09/14/2009     12345678     -2250000     (2,748,645.88)

      5BFZTD2     09/14/2009     12345678     -25000     (28,028.63)

      5BFZYG2     09/14/2009     12345678     14900000     15,217,233.42

      5BGDGN2     09/14/2009     12345678     -250000     (291,702.64)

      5BGDMP3     09/14/2009     87654321     -5000000     (4,838,020.83)

      5BGDMP3     09/14/2009     87654321     -10000000     (9,676,041.67)

      5BGDMP3     09/14/2009     87654321     15000000     14,514,062.50

      5BGFCY1     09/14/2009     87654321     -189000     (188,225.10)

      5BGFCZ3     09/14/2009     87654321     -100000     (99,790.00)

      5BGFDB4     09/14/2009     87654321     -416000     (413,442.00)

      5BGFDC8     09/14/2009     87654321     -555000     (553,335.00)

      5BGFDL4     09/14/2009     87654321     -20000     (19,760.00)

      5BGHLZ4     09/14/2009     87654321     3000000     3,257,354.17

      5BGHLZ4     09/14/2009     87654321     6000000     6,514,708.33

      5BGHLZ4     09/14/2009     87654321     -9000000     (9,772,062.50)

      5BGMRD8     09/14/2009     87654321     -1500000     (1,510,636.25)

      5BGMRD8     09/14/2009     87654321     1500000     1,510,636.25

        • Help with Matching up records records
          Data Kruncher

          Generally, when you need to analyze a dataset based on some kind of groupings or subtotals, you're looking a multiple pass solution, and this is the case here.

           

          In the first model/project, first define a new Key character field with:

          [SIZE=2]Account"-"[/SIZE][/CODE]

           

          Now build a Net numeric field with:

          [SIZE=2]Total-Share[/SIZE][/CODE]

           

          Create a new summary with Key, Account and Security Number as key fields and Net as your measure. Define your summary without subtotals or a grand total. Now (project) export the summary. Save and close the model and project.

           

          For the next model/project, open your export as the data source, and create a new Group character field with:

          [SIZE=2]if(Net=0,"Zero",

          if(abs(Net)<=25,"<=25",">25"))[/SIZE][/CODE]

           

          Create a summary using Group, Account, and Security number as key fields, with subtotals for each, and a grand total. The Net field is your only measure.

           

          Now you can see which clear out (zero balances), which are -25 to +25, and which exceed +/-25.

            • Help with Matching up records records
              adonis _

              Hi Kruncher

               

              My question is that based on your formula, you are subtracting Quantity from the Amount, based on seeing the Total-Share

               

              my problem is that i am trying to subtract across multiple records.

              if the absolute quantities match up, then subtract the amounts.

              so in essence

              examples (one that has a remaining amount, the other matches up)

              record 1) quantity = 10000 amount = 200.00

              record 2) quantity = -10000 amount = -200.05

               

              record 3) quantity = 20000 amount = 400.00

              record 4) quantity = -20000 amount = -400.00

               

              in example 1( records 1 & 2 would be removed because of the <=25 threshold but the sum of .05 (remaining amount would be brought into a misc total field which would add all like items)

               

              in example 2 (records 3 & 4 would be removed altogether because the sums = 0 and there is no remaining amounts to carry over to the misc field)

               

              items that are not equal to zero would be shown as a line item as those need to be investigated.  Basically trying to pair off the ones that match up and work with exceptions.

               

              please advise.

                • Help with Matching up records records
                  adonis _

                  based on what you provided the data would show up like this.

                  -22000     -22021.1     -21     <=25

                  -1     -1     0     Zero

                  1     1     0     Zero

                  0     -133.17     -133     >25

                  0     133.17     133     >25

                  154000     21921.32     -132079     >25

                  -50000     -55506.8     -5507     >25

                  50000     55506.8     5507     >25

                  -20000     -47274     -27274     >25

                  20000     47274     27274     >25

                  -5000     -116500     -111500     >25

                  5000     116500     111500     >25

                  -5560     -82864     -77304     >25

                  5560     82864.15     77304     >25

                  -20000     -138442     -118442     >25

                  20000     138442     118442     >25

                  -16     -663.2     -647     >25

                  -8100     -183520     -175420     >25

                  8100     183520.08     175420     >25

                  0     200     200     >25

                  -65000     -71147.12     -6147     >25

                  10000     10493.06     493     >25

                  -300000     -300015.29     -15     <=25

                  10000     9625.07     -375     >25

                  6000     5546.15     -454     >25

                  -20000     -20888.83     -889     >25

                  65000     58327.76     -6672     >25

                  20000     20160.02     160     >25

                  -4500000     -4727172.5     -227173     >25

                  4500000     4727172.5     227173     >25

                   

                   

                  however the recon that is being done currently is done from left to right when it needs to be up and down.  I dont know how to match up and down, i knew the component you mentioned but look for example the last 2 items.

                  quantities match up but sign is different, also the amounts match up.  if you see the net amount column as -227173 that is not right, and the >25 should = 0

                   

                  so in theory should only see 0 items and items that don't match up and the items where the quantities match and amount is less then $25.01 would be added to a misc total field.

                    • Help with Matching up records records
                      adonis _

                      if i do a sum by the following is what I get below (For the ones that equal 0 and amount is <=25, i need to delete the records, is there a function in Monarch which would exclude these records?  In addition, if the amount is $25 or less on both positive and negative signs, then I wanted to add these up and put them into a field called misc total) or perhaps thinking the other way, only extract data where quantity and amount are not in that threshold?

                       

                      Quantity  Amount

                      -22000     -22021.1

                      0     0

                      0     0

                      154000     21921.32

                      0     0

                      0     0

                      0     0

                      0     0.15

                      0     0

                      -16     -663.2

                      0     0.08

                      0     200

                      -65000     -71147.12

                      10000     10493.06

                      -300000     -300015.29

                      10000     9625.07

                      6000     5546.15

                      -20000     -20888.83

                      65000     58327.76

                      20000     20160.02

                      0     0

                      0     0

                      -20000     -23338

                      -5000     -5316.57

                      -75000     -77538.72

                      -8000     -8104

                      0     0

                      -25000     -28028.63

                      14900000     15217233.42

                      -250000     -291702.64

                      0     0

                      -189000     -188225.1

                      -100000     -99790

                      -416000     -413442

                      -555000     -553335

                      -20000     -19760

                      0     0

                      0     0

                      -5500     -4888.13

                      50     8579.5

                      80     3220.8

                      13     6111.17

                      125     5741.5

                      500     55090.46

                      50     5005

                      -10000     -18900

                      240     4680.49

                      300     9897

                      -2000     -10440.17

                      180     4498.56

                      0     -13200

                      16     663.21

                      -150     -6867.75

                      600     3246

                      160     7417.77

                      90     7089.3

                      0     -15999.99

                      -15000     -163918

                      -4500     -450

                        • Help with Matching up records records
                          Olly Bond

                          Hello adonis,

                           

                          In your second model, a filter with the expression abs(Net)>25 would exclude the data you don't need.

                           

                          Go to the table window in Monarch, select Data, Filters, New and build that there.

                           

                          Then in the summary window, edit the summary properties, look on the general tab, and make sure that this filter is applied.

                           

                          To make a sum called Misc of just these small amounts will require another two calculated fields, but it's fairly easy.

                           

                          Firstly, define Misc as if(abs(Net)<=25;Net;0), and define another field NewAmount as if(abs(Net)>25;Net;0). Then edit your summary to use these two columns as measures.

                           

                          In the Options > View menu, you can choose to not display zero values, which will make the summary clearer to look at.

                           

                          Best wishes,

                           

                          Olly