4 Replies Latest reply: May 15, 2014 9:51 AM by JMS _ RSS

    Comparing Data

    JMS _

      Hello,

       

      I'm a new Monarch v7 user and am wondering how I can use Monarch to compare two columns of data and extract items unique to one column only.  For example, my data is:

       

      col 1             

      1                 

      2

      3                 

      4

       

      col 2

      2

      4

       

      I would like the end result to show that 1 and 3 are missing from column two.  Using the duplicates filter under the advanced tab shows me the duplicates in a single column and I would like to filter out the items in col. 2 that are duplicated in col 1. 

       

      Thanks All for your help.

      JMS

        • Comparing Data
          Grant Perkins

          Hi JMS,

           

          The answers sort of depend on what you have to start from - are both columns on the same report for example.

           

          If not, can you you perform a lookup and filter where there is a match? (or no match ...). If you have V7 Pro you have a choice of internal or external lookup. External is like Excel vlookup. Internal would mean populating the table (cut and paste possible) in a calculated field.

           

          If both columns are in the same report are they sequential - as you presented them - or side by side?

           

          If side by side could both columns contain data unique to themselves? If not - i.e. the second column values will ALWAYS appear in the first column for example - then perhaps the multi-column process would work to give a single column and then filter for multiple occurrences in a summary.

           

          Key field properties Matching > Measure > Count "at most" 1 would seem to do it.

           

          A few ideas to be considering. The approach does depend on what you have to work with so any more information would help.

           

          Grant

           

           

          Originally posted by JMS:

          Hello,

           

          I'm a new Monarch v7 user and am wondering how I can use Monarch to compare two columns of data and extract items unique to one column only.  For example, my data is:

           

          col 1             

          1                 

          2

          3                 

          4

           

          col 2

          2

          4

           

          I would like the end result to show that 1 and 3 are missing from column two.  Using the duplicates filter under the advanced tab shows me the duplicates in a single column and I would like to filter out the items in col. 2 that are duplicated in col 1. 

           

          Thanks All for your help.

          JMS /b[/quote]

          • Comparing Data
            JMS _

            Hi, Grant

             

            The data consists of two columns side by side in a single report.  Col. 1 is a loooooong list of ids that were to be extracted.  Col. 2 is a list of the ids that were actually extracted.  I want to quickly compare the two and get a list of everything that did not get extracted.

             

            I checked out the Lookup solution you suggested but am unsure how to compare the two columns.

             

            Thanks again for your help.

             

            JMS

            • Comparing Data
              Grant Perkins

              The lookup option would mean 'extracting' one column and then comparing wiht the other based on a table. Not ideal for a long list if there is a quicker approach.

               

              If you can define the report as two identical columns (Multi Column Region feature) and populate the ids from both columns into a single column (field) in the table - which is what MCR should do - you should be able to create a summary for the ID field and check the 'count' (if it appears in only one column the count will be 1, if in both columns the count will be 2) and use the

               

              Key field properties Matching > Measure > Count "at most" 1

               

              concept or even the 'Unique' selection to leave only the unselected IDs separately listed from 'All others'.

               

              If MCR can't be made to produce the output you will need to find another way to get to a similar result I think.

               

              Grant

               

              Originally posted by JMS:

              Hi, Grant

               

              The data consists of two columns side by side in a single report.  Col. 1 is a loooooong list of ids that were to be extracted.  Col. 2 is a list of the ids that were actually extracted.  I want to quickly compare the two and get a list of everything that did not get extracted.

               

              I checked out the Lookup solution you suggested but am unsure how to compare the two columns.

               

              Thanks again for your help.

               

              JMS /b[/quote]

              • Comparing Data
                JMS _

                Hi, Grant

                 

                Using your ideas I stuffed everything into a single column, built a summary that counted the ids and using measure limited the results to items with only one result.  Works great!

                 

                Thanks very much for your help.

                 

                JMS