5 Replies Latest reply on Jan 6, 2012 10:29 AM by Robert Morton

    How to compare two sets of data?

    Zyz Iwi

      Would appreciate your help on a problem we’re facing.

       

      We have two large data sources, and we want to compare the two sets of data.  We have joined these two together on a unique id that is present in both.  In excel it would be a vlookup function... but this is not present as far as we can tell.  We have tried using an if statement however this aggregates the non-primary data source.

       

      I attach below some sample data, would really appreciate your help.

        • 1. Re: How to compare two sets of data?
          Richard Leeke

          A few options in the attached.

           

          The first 2 sheets use data blending to highlight the ones that match and don't match, using A and B as the primary in the 2 sheets.  This may not work if your data source is too large - and may not really support the type of analysis you want to do.

           

          The other two sheets use a custom SQL connection to join the 2 tables.  Really what you want is a "FULL OUTER JOIN" (i.e. a join which returns a row even if the row is missing in either of the tables).  Tableau doesn't directly support that type of join, so you have to do it as custom SQL.  Some databases support a join type of either "FULL OUTER JOIN" or just "OUTER JOIN" - but the MS JET engine (used for your spreadsheet) doesn't support either form.

           

          But you can still get the same effect by creating a UNION of a LEFT JOIN and a RIGHT JOIN.

           

          With the custom SQL approach you can do summary reporting, such as counting how many are present in both, missing from A and missing from B, as in the last sheet.

           

          Does that help?

          • 2. Re: How to compare two sets of data?
            Zyz Iwi

            Thank you very much for your reply.

             

            In Tableau, is it possible to join two tables if they are on two different data servers (MySQL) ?

            Or how can I create a Custom SQL data from two different data servers?

            • 3. Re: How to compare two sets of data?
              Robert Morton

              Hi zyz,

              Currently you can only join two tables which reside in the same database server. However some systems like SQL Server allow you to create a virtual (linked) table which internally connects to a separate database server. I don't know if MySQL has this facility.

              -Robert

              • 4. Re: How to compare two sets of data?
                Zyz Iwi

                Thanks for your reply, it seems even two tables in the same data server, but still cannot blending the data if they are in two different schema?

                • 5. Re: How to compare two sets of data?
                  Robert Morton

                  Hi zyz,

                  Some databases allow you to join between tables in different schemas, but unfortunately MySQL does not. Sorry my previous post was not clear on this point.

                  -Robert