6 Replies Latest reply on Feb 11, 2014 6:24 PM by Alban Spella-Barberet

    audit data from table, looking for differences

    Alban Spella-Barberet

      Hello,

       

      I recently joined the Tableau community and I am seeking help....

      I did a bit of search on the forum and I can't find a solution so far for my problem.

       

      I attached a screenshot of what I have so far.

      I have sites (siteID), each site gets antennaID and I have a value for each Antenna ID. The value can change depending on the revision.

      You can see on the example I built that Applethorpe got a value of 15 on revision A.4 and 2 on Revision A.5.

       

      I am trying to flag the values which have changed between A.4 and A.5.

      I don't need to do formula. I need to do an audit work. Basically what has changed between two revision.

       

      Can Tableau do that?

       

      Thanks.

       

      PS:

      I found that thread:

       

      Compare two data-sets for changes

       

      I am basically trying to do the same, but in my case everything is on the same excel file.

        • 1. Re: audit data from table, looking for differences
          kettan

          The query below used technique #6 in  The Cross Join Collection :

          .

          SELECT *

          FROM [Antennas$] a1, [Antennas$] a2

          WHERE NOT (a1.[Site ID] = a2.[Site ID] and a1.[Antenna ID] = a2.[Antenna ID] and a1.[Date] = a2.[Date])

          AND (a1.[Site ID] = a2.[Site ID] and a1.[Antenna ID] = a2.[Antenna ID])

          AND a1.[Date] < a2.[Date]

          AND a1.[Revision] <> a2.[Revision]

          .

          Formula explained:

           

          NOT (a1.[Site ID] = a2.[Site ID] and a1.[Antenna ID] = a2.[Antenna ID] and a1.[Date] = a2.[Date])

          This ensures that the same row is not compared with itself. No need for that 

          I have made an assumption that [Side ID] + [Antenna ID] + [Date] is primary key inclusive versioning.

           

          AND (a1.[Site ID] = a2.[Site ID] and a1.[Antenna ID] = a2.[Antenna ID])

          This ensures that the same is compared with the same.

          Here the assumption is that [Side ID] + [Antenna ID] is the primary key exclusive versioning.

           

          AND a1.[Date] < a2.[Date]

          This is to avoid duplicate rows. This condition probably needs to be refined when there are more than two versions so it only refers to the previous version. I believe a subquery is needed unless a pattern (such as always a month between the dates) or a numeric field (such as version number) could be used as a formula.

           

          AND a1.[Revision] <> a2.[Revision]

          This limits the list to where the Revision is changed.

          .

          See more in attached workbook and screenshot below.

           

          thread 138214 audit data from table, looking for differences.png

           

          Ps. I believe you will have good performance of mentioned CROSS JOIN if your data is stored in a real database such as SQL Server. However, if your data source is Excel or CSV (Jet SQL), you might get poor performance with only a few thousands rows.

           

          .

          Workbook Version:  8.1

          • 2. Re: audit data from table, looking for differences
            Alban Spella-Barberet

            Hello Kettan,

             

            I have tableau 8.0 ... could you export the workbook you created in 8.1 to a previous release so I can open it?

             

            Thanks in advance!

            • 3. Re: audit data from table, looking for differences
              Alban Spella-Barberet

              Hello Ketan,

               

              AND (a1.[Site ID] = a2.[Site ID] and a1.[Antenna ID] = a2.[Antenna ID])

              This ensures that the same is compared with the same.

              Here the assumption is that [Side ID] + [Antenna ID] is the primary key exclusive versioning.

               

              This is good if we assume that the sites and antennas are always the same in version/dates. Now, In my file, I added a row with an existing site but with a different antenna ID on the new revision.

               

              To see it coming in the result list, I need to delete that condition a1.[Antenna ID] = a2.[Antenna ID]. Therefore I can see all kind of combination appearing on the result list, a bit like a full join,

               

              Is there a way to make the query more smart, basically between two revision, a site can be added or removed, an antenna can be added or remove. I reckon the best is probably y to use the revision or the date as primary keys.

               

              My goal is really to find out the differences between two revisions. I am simply wondering if that's really possible as the number of combination is huge... It is like doing a left join, right join and inner join in a separated and merge the results. Is Tableau capable of this?

              • 4. Re: Re: audit data from table, looking for differences
                kettan

                The query below is hopefully something that works in your situation. I have stress-tested it by adding an extra version and changed revision to a numeric field to make it easier to include only "current and previous".

                 

                INSERTED and DELETED are real changes whereas UPDATED needs a filter to exclude those with no changes. This should be straight forward and can be done inside Tableau. I leave this part of the job to you.

                .

                SELECT 'UPDATED' AS [Type of Change], *
                FROM [Antennas$] cur
                INNER JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                  AND cur.[Antenna ID] = pre.[Antenna ID]
                  AND cur.[Revision Number] = pre.[Revision Number] + 1
                UNION ALL
                SELECT 'INSERTED', *
                FROM [Antennas$] cur
                LEFT JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                  AND cur.[Antenna ID] = pre.[Antenna ID]
                  AND cur.[Revision Number] = pre.[Revision Number] + 1
                WHERE pre.[Site ID] is null
                AND cur.[Revision Number] > (SELECT MIN([Revision Number]) FROM [Antennas$])
                UNION ALL
                SELECT 'DELETED', *
                FROM [Antennas$] cur
                RIGHT JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                  AND cur.[Antenna ID] = pre.[Antenna ID]
                  AND cur.[Revision Number] = pre.[Revision Number] + 1
                WHERE cur.[Site ID] is null
                AND pre.[Revision Number] < (SELECT MAX([Revision Number]) FROM [Antennas$])
                
                
                
                
                
                
                

                thread 138214 audit data from table, looking for differences - 8.0 - version number.png

                • 5. Re: audit data from table, looking for differences
                  Alban Spella-Barberet

                  Thanks a lot. I believe there is a limitation here. If I have 15 revisions on my file, the method above will take the cur. and compare to the pre. So if I want to compare version 10 with version 14 for example, then I reckon it has to be hardcoded on the SQL, right?

                   

                  Thanks.

                  • 6. Re: Re: audit data from table, looking for differences
                    kettan

                    if I want to compare version 10 with version 14 for example, then I reckon it has to be hardcoded on the SQL, right?

                    If I only knew, because limiting the comparison to two hand-picked versions is easier to code 

                    The technique used is SQL parameters. See more in attached files and code below.

                     

                    SELECT 'UPDATED' AS [Type of Change], *
                    FROM [Antennas$] cur
                    INNER JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                      AND cur.[Antenna ID] = pre.[Antenna ID]
                    WHERE cur.[Revision Number] = <Parameters.Newer Version>
                    AND pre.[Revision Number] = <Parameters.Older Version>
                    UNION ALL
                    SELECT 'INSERTED', *
                    FROM [Antennas$] cur
                    LEFT JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                      AND cur.[Antenna ID] = pre.[Antenna ID]
                      AND cur.[Revision Number] = pre.[Revision Number] + (<Parameters.Newer Version> - <Parameters.Older Version>)
                    WHERE pre.[Site ID] is null
                    AND cur.[Revision Number] = <Parameters.Newer Version>
                    UNION ALL
                    SELECT 'DELETED', *
                    FROM [Antennas$] cur
                    RIGHT JOIN [Antennas$] pre ON cur.[Site ID] = pre.[Site ID]
                      AND cur.[Antenna ID] = pre.[Antenna ID]
                      AND cur.[Revision Number] = pre.[Revision Number] + (<Parameters.Newer Version> - <Parameters.Older Version>)
                    WHERE cur.[Site ID] is null
                    AND pre.[Revision Number] = <Parameters.Older Version>
                    

                     

                    thread 138214 audit data from table, looking for differences - 8.0 - parameters.png