12 Replies Latest reply: May 15, 2014 10:14 AM by JLain _ RSS

    How to combine two lines of data using a calculated field

    JLain _

      Here's the scoop. I have an AR aging report that is bringing in cash application information when the cash application leaves a short paid balance on the original invoice.

       

      Example is pinned below. Inv no 9092404082 for $281,840.04 had a payment applied leaving a short paid balance of 1,266.99. I want to combine these records from my source aging file via hopefully a calculated field and create an open amount column for this invoice showing the 1,266.99 amount and thus via export eliminating the cash application entry.  Help

       

                1,266.99           

      Invoice #     Invoice Date     Inv# Amount     FI Doc# #     Doc#Type

      9092404082     8/31/2011     281,849.04     103822307     RV     

      9092404082     12/15/2011     -280,582.05     1400217276     ZZ

        • How to combine two lines of data using a calculated field
          Grant Perkins

          What do you see as you DETAIL record for this report?

           

          If I take the sample as being the total record available to you (and I'm guessing this may be a subset of something else but let's deal with what I can see),

           

          The discrepant value (1,266.99) is the detail record.

           

          The "Invoice #     Invoice Date     Inv# Amount     FI Doc# #     Doc#Type" line might represent a proxy for that record if modelling a unique trap is otherwise problematic.

           

          I would assume that each such record will present 2 lines in report to represent the discrepancy although I would guess it may be possible that there are more than 2 lines.

           

          If, for now, we assume there are always only 2 lines, the template needs to map the fields in both lines and create the fields in a single detail record. From there you can calculate what you need.

           

          From the example (but perhaps not in the real report?) the columnar format of the fields is not exact but that can be resolved with slice and dice techniques provided by Monarch Functions.

           

          Before I dive into more detail which you may not need (or that may be erroneous to the problem if I have misunderstood), how much sense does this make so far?

           

           

          Grant

            • How to combine two lines of data using a calculated field
              JLain _

              Grant,

              If I'm reading your response correctly you are not far off. The fields I input are small subsets of line data on my report table. Each record is not represented by 2 lines...only those invoices where cash has been applied.

               

              So, in the example provided the correlation I have beside the fact that the invoice number is repeated is that the FL Doc# begins with a 10 and the Doc Type is RV each as it corresponds to an original invoice. When cash is applied, the invoice no is repeated on the report and the FL Doc # begins with 14 and the Doc Type is ZZ.  I believe I need to create a calc field to capture the net amount of 1266.99. I just dont know how to have the calc scan the report to net the positive and negative amounts when these fields are represented on the report. The second quandry is that there are just invoice lines with no payment information attached. So if I had 3 lines of my report and 2 were the items below and one was another invoice without payments I would want to want to show inv 9092404082 with inv date of 8/31/2011 with FL doc # 103822307 etc.. with an open amount field of 1,266.99. The second item to export would be the other invoice.

               

              Invoice #      Invoice Date     Inv# Amount     FI Doc# #     Doc#Type

              9092404082     8/31/2011     281,849.04     103822307     RV     

              9092404082     12/15/2011     -280,582.05     1400217276     ZZ     

              9092277091     8/18/2010     35,871.42     103299113     RV     

               

              So my export when all is said and done is

              9092404082          8/31/2011             1,266.99             103822307          RV

              9092277091          8/18/2010            35,871.42             103299113         RV

               

              So the export is probably easy enough to create a qualify to exclude all "ZZ" doc types...but only after that amount has been used to net against the original invoice amount.

               

              Hope this explanation helps?

                • How to combine two lines of data using a calculated field
                  Olly Bond

                  Hello JLain, hello Grant,

                   

                  I may be being slow on the uptake here, but what's wrong with a summary where the invoice number is the key and the measure is Sum(Amount)?

                   

                  Best wishes,

                   

                  Olly

                    • How to combine two lines of data using a calculated field
                      JLain _

                      Olly

                      Hmm...I had thought about trying that...just might work. stay tuned

                        • How to combine two lines of data using a calculated field
                          JLain _

                          The summary idea is only duplicating what the record table is doing...it did not combine the two duplicate invoice items into a single amount due?

                           

                          The summary parameters I used

                           

                          Filter:     No Filter

                           

                          Key fields:     Invoice #

                           

                          Item fields:     Customer #, Customer Name, Invoice Date, Plant Description, Profit center, Reference Document, Doc#Type, FI Doc# #

                           

                          Measures:     SUM(Inv# Amount), SUM(Current), SUM(Days 1-30), SUM(Days 31-60), SUM(Days 61-90), SUM(Days 91-120), SUM(Days 120-150), SUM(Days 150-180), SUM(Days 180-360), SUM(1 Years), SUM(2 Years), SUM(3 Years), SUM(4 Years), SUM(5 Years), SUM(6 Years), SUM(7 Years)

                            • How to combine two lines of data using a calculated field
                              Olly Bond

                              Hello JLain

                               

                              That's how summaries work - specifying Item fields makes the summary show one row for each item. You only get to aggregate data using Keys. In your case, there are two approaches that will work.

                               

                              If your records are in Invoice # order, you might get away with a template that grabs the part payment(s) as part of the detail, and then you can do all the work with calculated fields, but you'll be working with "invisible data" functions like Textline(), and if you hit records with multiple part payments then it will get messy. Alternatively, just use two models. Model A will export two data sets - the filtered table with the non-ZZ records, and a summary (just with a key and some measures, no item fields) that is set to use "no filter". Model B then opens the filtered table output from Model A, and makes an external lookup using Invoice # as the joining field to pull in the aggregated totals from the summary.

                               

                              Hope this helps,

                               

                              Olly

                                • How to combine two lines of data using a calculated field
                                  JLain _

                                  Ah ha.  that must be why when I use the drill up I get the the collapsed amount that I need...meaning one invoice no and the agregate amount.

                                   

                                  So if I understand correctly..I export the summary which is just the invoice no and net dollar amount.

                                   

                                  Ok, good idea. Let me give it a go

                                   

                                  Tks

                                    • How to combine two lines of data using a calculated field
                                      JLain _

                                      one more question....any way to create both the first export of the summary page and the external lookup within the same project?

                                      • How to combine two lines of data using a calculated field
                                        JLain _

                                        Olly and Grant,

                                        The summary export and external look up worked.  Just need to figure out now how to make it work within a project. Great help though.....you probably hear it alot, but thanks again for the prompt responses

                                          • How to combine two lines of data using a calculated field
                                            Olly Bond

                                            Hello Jlain,

                                             

                                            One project won't do - you should use two for this. A project contains three things - the name of the input file, the name of the model file, and the definitions of the export(s).

                                             

                                            Best wishes,

                                             

                                            Olly

                                            • How to combine two lines of data using a calculated field
                                              Grant Perkins

                                              Olly and Grant,

                                              The summary export and external look up worked.  Just need to figure out now how to make it work within a project. Great help though.....you probably hear it alot, but thanks again for the prompt responses[/QUOTE]

                                               

                                              Many thanks for your kind words.

                                               

                                              Your profile indicates that you are using Version 8 - is that still the case? (For reference in case it affects any ideas further down the tracks.)

                                               

                                              In a 2 model scenario using Monarch only (ie, no Data Pump available) treat each part of the process separately (as Olly has indicated) but you can make the process happen by generating a simple scripted batch file to run each project one after the other. That's basically a Windows function and just requires a small amout of  Monarch related information to make it happen.

                                               

                                              A single pass model may be possible but, as Olly pointed out, there may be some occasional anomalies that prove tricky to find and/or eliminate from the process. So if you are OK with the multiple model/project approach it is probably easier (and more flexible for any future tweaks that may be identified) to take that route at this point in time.

                                               

                                               

                                              Grant