2 Replies Latest reply: May 15, 2014 10:09 AM by elginreigner _ RSS

    Can Monarch 10.5Pro compare values in two rows

    Rick Conklin

      Good morning,

       

      Suppose a field for invoice number on each row of data. I need to look for missing invoice numbers. 

       

      This would be similar to looking at a bank statement that lists the check numbers which have cleared.  My bank puts a star (*) when the sequence has a gap between check numbers

       

      So, I would like to compare consecutive rows and somehow highlight the gap between invoice numbers.

       

      Any ideas on how to do this with Monarch 10.5-Pro?

       

      Thanks

      Rick Conklin

      Wichita, KS

        • Can Monarch 10.5Pro compare values in two rows
          Data Kruncher

          As Monarch cannot behave like a spreadsheet and compare or otherwise access values from one row to another, we're left with devising other means to prepare this type of activity.

           

          I'd develop a simple list in Excel of all of the invoice numbers that you want to review, and then, using Monarch, I'd export a list of invoice numbers found in your report.

           

          Now open the Excel list as database source, and prepare an external lookup to the exported invoice numbers, importing ReportInvoiceNumber. Where ReportInvoiceNumber is null, it wasn't found in your report.

           

          Now you can use a calculated field to build a message:

          If(IsNull(ReportInvoiceNumber),"Missing","Included")[/CODE]

           

          It likely shouldn't take more than a couple of minutes to prepare.