15 Replies Latest reply: May 15, 2014 9:56 AM by Mike Urbonas RSS

    Transactional Comparison

    alexcn _

      Hey there,

      Well I thought I would try and get this in before that Friday feeling grabs hold of us all - I am trying to write a Monarch model that compares two transactional data files and produces an exception report with the differences. Basically this exception report should only contain details of the missing transactions from the right file when compared to the left - has anyone done anything like this or is it even possible?

      Many thanks!

        • Transactional Comparison
          Grant Perkins

          Hi and welcome!

           

          It certainly should be possible one way or another.

           

          Are your two files in the same format and extractable by the same model?

           

          Do they share common keys, at least in part, which could be used to do a direct comparison or is there work required to generate a key before that can be done?

           

          There is/was an example of a comparison process for two reports with the same format posted here and described in the Monarch Report a few years ago. It would have used an earlier Monarch version and it is likely there will be shorter steps available using more recent version.

           

          If this is your scenario I will seek out the links. There are likely to be other more recent examples in the forum as well.

           

          On the other hand of your two reports are not in the same format a different approach will be required at the start of the process to extract for common format or for lookup purposes using a suitable unique key.

           

          In the first example  - same report format - the way forward is likely to be to read in both reports than use a summary and for the count of the number of records for the appropriate key field(s) combination(s) filter for the count = 1 and the one record is from the 'right' file.

           

          That at least seems to describe it in words I think. If this is generally correct we just need to translate that in to a Monarch Summary.

           

          It is just possible that a multi-step process might be required or easier to work with for your needs but we can consider that later.

           

          HTH.

           

           

          Grant

          • Transactional Comparison
            alexcn _

            Are your two files in the same format and extractable by the same model?

            : They contain the same data but are not in the same format - however it would be relatively easy for my to have two Monarch models preparing the data so it forms itself uniformly and then have the exports for these TWO as the inputs for the comparison.

             

            Do they share common keys, at least in part, which could be used to do a direct comparison or is there work required to generate a key before that can be done?

            : Unfortunately only one side (lets call this the LEFT) has a key as this is assigned after the fact.  The system producing the transactions is providing the RIGHT side.  Essentially these are financial markets transactions and so are Buy/Sell indicator, Trade Quantity, Trade Price, Commodity, Trade Date.

             

            There is/was an example of a comparison process for two reports with the same format posted here and described in the Monarch Report a few years ago. It would have used an earlier Monarch version and it is likely there will be shorter steps available using more recent version.

            : I should very much like to see the example(s) if you can find it(them)

             

            On the other hand of your two reports are not in the same format a different approach will be required at the start of the process to extract for common format or for lookup purposes using a suitable unique key.

            : creating these preparatory steps is relatively straight forward, I am thinking that perhaps key would be a combination of all the underlying fields? Admittedly though there COULD be steps where a trade quantity, commodity AND price are repeated on more than one line!

             

            In the first example  - same report format - the way forward is likely to be to read in both reports than use a summary and for the count of the number of records for the appropriate key field(s) combination(s) filter for the count = 1 and the one record is from the 'right' file.

            : This seems to make sense yes.

             

            Many thanks Grant - its much appreciated!

            • Transactional Comparison
              Grant Perkins

              Originally posted by alexcn:

              Are your two files in the same format and extractable by the same model?

              : They contain the same data but are not in the same format - however it would be relatively easy for my to have two Monarch models preparing the data so it forms itself uniformly and then have the exports for these TWO as the inputs for the comparison.[/b][/quote]That sounds like a good way to go. If you can transform one to be the twin (in terms of format) of the other you can then treat them as 2 versions of the same input file. Load both files, set up a summary that effectively links as many fields as required to give a unique record for each combination at the level of detail necessary. Use count as a measure and also have something to identify which source the records are from. Exclude any with a count of 2 and then any that are from the 'wrong' report.

               

              Originally posted by alexcn:

              Do they share common keys, at least in part, which could be used to do a direct comparison or is there work required to generate a key before that can be done?

              : Unfortunately only one side (lets call this the LEFT) has a key as this is assigned after the fact.  The system producing the transactions is providing the RIGHT side.  Essentially these are financial markets transactions and so are Buy/Sell indicator, Trade Quantity, Trade Price, Commodity, Trade Date.[/b][/quote]Not a problem as long as the 'key' can be derived from the data and it sound like that is possible. The key is any combination of fields that is required to match records from the two reports.

               

              Originally posted by alexcn:

              There is/was an example of a comparison process for two reports with the same format posted here and described in the Monarch Report a few years ago. It would have used an earlier Monarch version and it is likely there will be shorter steps available using more recent version.

              : I should very much like to see the example(s) if you can find it(them)[/b][/quote]I'll see what I can find.

               

              Originally posted by alexcn:

               

              On the other hand of your two reports are not in the same format a different approach will be required at the start of the process to extract for common format or for lookup purposes using a suitable unique key.

              : creating these preparatory steps is relatively straight forward, I am thinking that perhaps key would be a combination of all the underlying fields? Admittedly though there COULD be steps where a trade quantity, commodity AND price are repeated on more than one line![/b][/quote]OK. I assume this means possible dual records from one or other side of the reports? Would that mean that the record would count as needing 2 (or more) matches for you purpose or could be treated as just one? Whatever there will be a way around it.

               

              Originally posted by alexcn:

              In the first example  - same report format - the way forward is likely to be to read in both reports than use a summary and for the count of the number of records for the appropriate key field(s) combination(s) filter for the count = 1 and the one record is from the 'right' file.

              : This seems to make sense yes.

               

              Many thanks Grant - its much appreciated! [/b][/quote]A slightly different idea would be to seek out the (virtual) key fields and for one of the reports export the list to a lookup table (2 columns, second one a simple flag for 1 for example.)

               

              The model for the second report ("right" file)could then make a lookup call and populate a calculated field with "1" if the field exists in the other file.

               

              Filter out the lines with "1" fields and the remainder are those with no match. That gives a 2 stage process but no requirement for using the summary feature.

               

              As I recall the example I am going to look for dealt with the potential for non-matches on BOTH sides. If you don't need that then the 2 stage lookup solution may make much more sense.

               

              HTH

               

               

              Grant

              • Transactional Comparison
                alexcn _

                Morning Grant,

                 

                Many thanks for your follow up posting, it's greatly appreciated.  Hope you had a good weekend. However I had a few questions if you wouldnt mind:

                 

                1) Yes I would need to prepare a report detailing the records that are missing from both sides of the comparison - I am assuming that would lead me to follow the first example then.

                2) In terms of this example - loading both files - I am loading one as a master and the other as a lookup i.e. horizontally or simply having them joined to the end of each other i.e. vertically with a column indicating the source?

                 

                Many thanks in advance,

                 

                Alex

                • Transactional Comparison
                  Grant Perkins

                  Originally posted by alexcn:

                  Morning Grant,

                   

                  Many thanks for your follow up posting, it's greatly appreciated.  Hope you had a good weekend. However I had a few questions if you wouldnt mind:

                   

                  1) Yes I would need to prepare a report detailing the records that are missing from both sides of the comparison - I am assuming that would lead me to follow the first example then.

                  2) In terms of this example - loading both files - I am loading one as a master and the other as a lookup i.e. horizontally or simply having them joined to the end of each other i.e. vertically with a column indicating the source?

                   

                  Many thanks in advance,

                   

                  Alex /b[/quote]Alex,

                   

                  For question 1 have a look at these references to see if they help.

                   

                  I would guess that a few years and versions later there may be some short cuts available but the theory should still be sound.

                   

                  For question 2 - either methods could produce results. However if you have discrepancies to report from both reports combining them into a single commonly formated "report" and then using a count mechanism where, in simple terms, a count of 2 = a match and a count of 1 = no match, could allow you to get a single list showing non-matches in both files. But that assume you are able to construct a pseudo key from the component data that gives a one to one match.

                   

                  If you have, say, 1 transaction in report A  balanced by 5 transactions which sum to that one in report B life could be a little more interesting. You may need to pre-summarise B before combining the reports.

                   

                  There may be a number of successful approaches. Each is likely to require at least 2 step in the process. Which is the most efficient and effective for you (best result for least development and future maintenance effort) may not become clear until you have tested the theories.

                   

                  If you are going to manipulate the reports into a common format and combine them be sure you make the field sizes large enough to contain the maximum sizes that may come up in the reports - don't assume that the sample files you are developing with are always going to be representative of the field data sizes that will be reported!

                   

                  HTH.

                   

                   

                  Grant

                   

                  Edit: - Whoops - forgot the references!

                   

                  [url="http://www.datawatch.com/pdf/products/monarch/Newsletter_Fall_2001.pdf"]http://www.datawatch.com/pdf/products/monarch/Newsletter_Fall_2001.pdf[/url]

                   

                  Page 5

                   

                  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000901"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000901[/url]

                   

                  [size="1"][ October 25, 2006, 06:21 AM: Message edited by: Grant Perkins ][/size]

                  • Transactional Comparison
                    alexcn _

                    I am thinking i am going to kick myself when you answer this post - but I am stumped as to filtering the summary view AFTER the results have been produced?

                     

                    If you apply a filter to the summary definition then it prefilters the information coming in from the Table view, and obviously given your suggestion for transactional comparison - I need to filter out all records that have a match not equal to 2?

                     

                    Cheers!

                    • Transactional Comparison
                      Grant Perkins

                      Originally posted by alexcn:

                      I am thinking i am going to kick myself when you answer this post - but I am stumped as to filtering the summary view AFTER the results have been produced?

                       

                      If you apply a filter to the summary definition then it prefilters the information coming in from the Table view, and obviously given your suggestion for transactional comparison - I need to filter out all records that have a match not equal to 2?

                       

                      Cheers! [/b][/quote]Hi Alex,

                       

                      There are a couple of approaches to this.

                       

                      As I recall the Michael Hansen solution to a similar requirement includes all the records but highlights the differences by colouring them using the colouring and limits attributes in the measure properties.

                       

                      Monarch 8 Pro allows you another option which involves going into the summary definition, right click on an appropriate key field to get to the properties and then on the MATCHING tab. You should then be able to set value for the MEASURE to match to.

                       

                      One display constraint here is that Monarch will always report for all records but will bundle everything outside your MATCHING criteria into an ALL OTHERS accumulation field. Bearing in mind the nature of the interactive summary function this is sensible but may not be what you want for any output.

                       

                      In the case that your final output requires only the exceptions I think the easiest approach would be to export the summary to a report - with or without the MATCHING criteria set - and then read that with another simple Monarch model filtering the lines on the measure value you require or, if you have already set the MATCHING values, simply ignoring lines which report "All Others" or whatever you have chosen to call them.

                       

                      There are some other ideas - like sorting on the measure value as well so retaining all records in the summary but grouping the significant ones by their value.

                       

                      See where these ideas take you. If you need something different we can probably come up with something else with a little more thinking.

                       

                      HTH.

                       

                       

                      Grant

                      • Transactional Comparison
                        alexcn _

                        Many thanks Grant.

                         

                        I already tried the Matching option approach but unfortunately this is only available on non-numeric fields and the summary field (being a count) is of course numeric! I even tried modifying the aggregation formula in order to convert the value to a string - but unfortunately the expression builder window wont allow that type of function.

                         

                        I did think of the export/import approach but since I had already had two pre-processing models to prepare the two input files in the appropriate common format, then the summary model I of course wanted to avoid a fourth.

                         

                        Any other ideas would be greatly appreciated.

                         

                        Cheers

                        • Transactional Comparison
                          alexcn _

                          I probably should add that the exceptions are the only values that i will require, and probably be able to handle given that the number of transactions in the comparison could run into 10,000's of lines and therefore something which would be beyond the realms of printing!

                          • Transactional Comparison
                            Grant Perkins

                            Alex,

                             

                            Set your main key field (character I assume, or could be if you create a calculated field to convert a number to a character field and then use that as the key) to MATCH on MEASURE (ie the count info) and then specify the value you want to match on.

                             

                            The summary should then give you your itemised list of no matches (or matches depending on which way you do it!) and then a single line for "All Others" or whatever you wish to call it.

                             

                            I don't seem to have an obvious problem using a numeric field either, so long as it is a key field in the summary.

                             

                            When I mention printing an output prior to the next step I am really suggesting 'print' it to a disk file or exporting it as a fixed width columnar disk file. Either way it can then be read back into Monarch directly. Multi step processes can usually be automated (using a scripted batch file or some program code) once the component parts of the process are know to work. In which case the number of steps becomes less important to the user.

                             

                            If you still can't get it to work we may need to play around with some representative mocked up data - or real data if it can be made available.

                             

                            HTH.

                             

                             

                            Grant

                            • Transactional Comparison
                              Mike Urbonas

                              Hi Alex and Grant.

                               

                              Might this early Monarch Report article be of any help?

                               

                              "Monarch Tips & Tricks:

                              Use Monarch Summary Capabilities to Compare Two Reports for Differences" - from Fall 2001 Monarch Report:

                               

                              [url="http://www.datawatch.com/pdf/products/monarch/Newsletter_Fall_2001.pdf"]http://www.datawatch.com/pdf/products/monarch/Newsletter_Fall_2001.pdf[/url]

                              • Transactional Comparison
                                Mike Urbonas

                                smile.gif[/img]

                                • Transactional Comparison

                                  Hey Mike,

                                   

                                  I just wanted to say thanks.

                                   

                                  You just solved a situation I had with your Tips and Tricks from the Fall 2001 Newsletter related to Comparing Two Reports for Differences.

                                   

                                  I have to do this monthly and could not figure out an automated way to do it so I have been doing it manually for about a year and it takes me 3 or 4 hours each month. Now I can do it in about 10 minutes and be on my way; great tip.

                                   

                                  I love this Monarch Forum. I read it when I have time and I usually pick up some ideas from time to time. Monarch saves the day again.

                                   

                                  Thanks again,

                                  Dan Kinney

                                  • Transactional Comparison
                                    Mike Urbonas

                                    Glad to hear it Dan.

                                     

                                    I am working on coming up with a better way to enable visitors of this forum search back issues of the Monarch e-newsletter instead of relying on Google (some issues for example do not seem to be on Google's radar for some reason and do not appear).

                                     

                                    More on that hopefully soon.

                                    Mike