3 Replies Latest reply: May 15, 2014 10:08 AM by guyporter _ RSS

    Data Validation

    guyporter _

      Another question on Data extracts using Monarch V 9. I am extracting sales ledger information to a table that is then uploaded to another application. I need to validate the data extracted to the table and have tried using the data summary window to get the total number of records extraced and the total of a column of numbers to see if the total extracted matches the total on the report. So far I have been unable to do this in Monarch but have exported the date to Excel and added it up there. Is there a way to get column totals and /or count in The Monarch table window or summaries?

        • Data Validation
          Olly Bond

          Hello Guy,

           

          You can export a summary with totals by checking the "Insert Total Line" checkbox on the General tab of the Summary Definition dialog. This will default to showing totals for all measure columns. Adding a measure defined as "count" (available in the fields tab of Summary Definition) will give you a total row count.

           

          Beware - if you select key fields which are not unique then Monarch will aggregate the measures when summarising. To be certain to avoid this, you can define a calculated field as Recno() and use this as a hidden key in your summary. You'll then be certain that the number of records in the total of the count column equals the number of records in your table, presuming that the same filter is used in table and summary.

           

          Data verification routines like this frequently require two passes - once to take the data in, summarise it and get a handle on how many records there should be, then export that data and use it as a lookup for a second pass on the data.

           

          HTH,

           

          Olly

          • Data Validation
            Nigel Winton

            Guy

            In the Summary Definition Window on the General Tab you can select Insert Total Line. This will put a total at the bottom of the page. You can change the type of calculation by selecting any of the Measures and then under the calculation tab choose your calculation.

            And to enhance it further you can select any of the Key Fields and under the Display Tab you can select Insert Sub Total which will subtotal by that Key Field.

            The count field is also in all summaries, which may also prove useful to you

             

            Regards

             

            Nigel