5 Replies Latest reply on Jan 15, 2019 11:13 AM by Roberto Bustos

    New User: Table Question

    Roberto Bustos

      Hi everyone.

       

      This question for sure will be easy for most of you but I can't seem to figure out how to start on this.  So this is the situation:

      1. First of all, my source is an excel export file from Oracle P6.  Mostly schedule data.
      2. 1 excel database file with 2 worksheet as sources.
      3. Both worksheets are identical in format and heading/columns.  Only difference are the values.  There is one column that is equivalent between the two files.  The Acitivy ID are unique identifier that is the same across both worksheet.
      4. I attached 2 files, database sample.jpg for how my database look and output.jpg for what I am hoping to achieve.
      5. Database sample.JPGOutput.JPG
      6. So in the right image, I'm hoping to display the values from the 1st source (base) and compare it to the 2nd source (revised).

       

      How do I get to do this?  Right now what I'm doing is prepare a separate worksheet for base and revised and combine them in dashboard.  Can I do this in just one worksheet?

      Also is there a limit to how many measured values you can make?  I can't add more to these 4 items:

      Mesaured Values.JPG

      Thanks everyone.  Really appreciate it.

        • 1. Re: New User: Table Question
          Joe Oppelt

          Blend the two data sources on the common field.

           

          Then you can just drag the measure from the secondary source onto your Measures shelf.  It will have to be used as an aggregate (such as SUM or MIN or COUNT, etc.)  Tableau does the work for you.

           

          You didn't attach any files or I would have hacked up a simple example for you. Can you attach two sample file (and you can replace any proprietary data with bogus values.)  I don't need all you data.  Just a few rows from a few examples of the key field that is the same in both files.

           

          And what version of Tableau are you using.   I'll upload an example that you'll be able to read if I know your version.

          • 2. Re: New User: Table Question
            Joe Oppelt

            PS:  There is no limit (that I know of) to the number of measures you can drag into the Measures shelf.  It's certainly not 4 if there is a limit!

            • 3. Re: New User: Table Question
              Roberto Bustos

              Wow thanks for the quick response!

              Here's a sample excel file with 2 worksheet.

              So between those sheets, the Activity ID column are equal.  So what I'm hoping for in a table is hopefully like this:

               

              Output2.JPG

              Activity Count - this can be the total number of activity IDs between the 2 sources.

              Successor Count - is the number of successors between the two.  Same as predecessors and constraints.

               

              I can't seem to figure out how to combine all those aggregates into one single table in a worksheet.

               

              Thanks in advance!

              • 4. Re: New User: Table Question
                Joe Oppelt

                I saved this as a 10.5 workbook since you didn't specify a version.

                 

                In your case it's more than just a blend as I initially suggested.

                 

                I made two separate data sources from the two sheets, and I made a 3rd data source that does UNION ALL of the two.

                 

                The UNION essentially adds one source to the other.  Where there are common IDs between the two, you'll get two rows in the UNION data source.

                 

                Sheet 1 is simply a look at BASELINE.  And I blended Update-14, and then gave a value where the ID from Baseline is also found in 14.

                 

                Sheet 2 is the opposite.  Both sheets show that there are common IDs, and also that each data source has some unique ones.

                 

                That's why I made the Union.  Sheet 3 displays all IDs, and the count of rows for each one.  Where the value is 1, that's a unique ID in one or the other data source.  Where it's 2, we have that ID in both.

                 

                Sheet 4 blends the two individual data sources to the UNION data source.

                 

                Notice the list of measures.  (More than 4!)  The first is the same value I displayed in sheet 3.  The second (scroll over the second pill in the measures list) has a value if there is a row in Baseline.  The third has a value if there is a row in 14.  Notice the header on the sheet.  The user can't tell which value is which.  So Next I made a calc in the UNION data source.  See [Records from BASELINE].  To create this, started it when I was in the UNION data source.  Then once the calc edit window was open, I clicked on the BAASELINE data source and dragged [Number of records] from that data source into the calc editor.  Tableau nicely created the whole syntax for me.  Now I'm pulling the same value from the BASELINE data source that the second pill on the Measures shelf was getting.  Only it's my own calc, and I can name it whatever I want.  And you can see in Sheet 4 that I have that name in the header, and the value for my calc matches the value that the second Measures pill has.

                 

                Look at [Records from 14].  It does the same thing, only it pulls the value from the 14 data source.

                 

                At the bottom of Sheet 4 I added totals.

                 

                Look at Sheet 4(2).  Here I took [Activity ID] off the sheet.  Now I just get rolled up sums.  There are 2311 records in Baseline.  There are 2221 that have values in both data sources.

                 

                Go back to Sheet 4.  Look at the calc for [Records in both].  This works on Sheet 4 because all the IDs are listed on the sheet.  But if you were to add that to Sheet 4(2), you would get a null there.  That's because [Records in both] has to evaluate at the ID level.  When  ID is off the sheet as it is in Sheet 4(2), tableau only sees the rolled up values.  2311 is not equal to 2270, so we get null.

                 

                So I created [Records in both V2].  Take a look at that.  It is looking at a FIXED LOD value.  These get evaluated at the specified level.  Here I specified [Activity ID].  On every Activity ID this calc pulls the number of records for that activity ID behind the scenes.  My calc says if the value is greater than 1 then set the value 1 (else leave it null.)  Now putting SUM([Records from both V2]) can get rolled up on Sheet 4(2), and you'll have your number of common IDs in both data sources.

                 

                Using these, you can do the kinds of math you need.  I'm not clear on what a successor is.  If it's an ID that is in both, then your value is 2221.

                 

                But now let me show you something different.

                 

                I added another column to both your excel sheets.  I called it "Source".  For the Baseline sheet I set it to "B", and for the 14 sheet I set it to "14".  You will see Source in the UNION data source.  I made a new set of calcs in the union data source.  They are all in Dimensions, and they all start with [Records ...].  I don't know if you have the liberty to add that column in your own data, but with that column I can eliminate all the blending, and just work with one data source.  On Sheet 6 I displayed a bunch of values.  My calcs can identify which Activity IDs are in which original excel sheets.  I can tell you which are unique to Baseline, which are unique to 14, which are in both.  This makes your analysis far easier, especially if your goal is to count up IDs that are in both, or are in just one sheet, etc.


                See attached.

                • 5. Re: New User: Table Question
                  Roberto Bustos

                  Wow!  This is quite a detailed response.  Thanks a lot Joe.  I'm going to look at it and try to understand how you did these.  Will probably get back with some more questions.  Thanks again!