10 Replies Latest reply on Aug 21, 2018 9:48 AM by Ken Flerlage

    Unmerge Second and Third Join

    Matt Youngberg

      Hi everyone!

       

      I'm working with three different datasets to create my visualizations. The first one is a date scaffold (excel) that will allow me to create graphs with 0 values. The second is an excel with entries for a bunch of different events. The third is also an excel with a bunch of entries of interviews that will take place.

       

      I'm wanting to put all three of these in the same data source with both the events and interviews merging on my date scaffold. Here is a screenshot of a hypothetical workbook where I essentially recreated the problem:

       

      Tableau forum.PNG

       

      I have circled what is going on here. When I create the merge, the events and interviews are merging on the same entry of the date scaffold. I don't want this to occur, because, in my larger, real scenario, the data source starts exploding, creating duplicates if we have more than 1 event on a single date. In that case, using the same join, the dataset will create extra dates within the scaffold to accommodate the multiple events. However, then when I insert the third sheet, will duplicate all interviews on that date across every single date that matches.

       

      My question, then, is this: How do I get tableau to simply concate these two excel sheets without merging any of the data between my second and third data source? This is more-or-less what I'd want it to look like, based off the image above:

       

      Tableau forum 2.PNG

       

      Any help would be greatly appreciated. I attached a packaged workbook in which to hopefully work out the answer.

        • 1. Re: Unmerge Second and Third Join
          Ken Flerlage

          Your workbook is prompting me to log onto Box, so I don't think the data has been properly saved with the workbook. Can you reattach it?

          • 2. Re: Unmerge Second and Third Join
            Ken Flerlage

            I worked up a small sample of your data though. Can you try unioning the Events and Interview data into a single data set, then join it to the Date Scaffold.

             

            You'll end up with your events and interviews in the same set of columns, but if you need them separated, it's easy enough to create calculated fields based on the Table Name/Sheet. Of course, you'd need to find a way to make sure that your events table and interviews table have the same structure so you can do the union.

             

            See attached sample data set and workbook.

            • 3. Re: Unmerge Second and Third Join
              Vinnie Ahuja

              You should be able to do this with a union:

               

              Select "New Union" and add your Interview and Event data sheets to the union.  This will create one data source that either has an interview or an event on each line but not both.  Then you'll want to outer join this single source of data to your date scaffold.  You will need to use a Join Calculation on the Union side of the join since each row has only one date but not the other:

               

              IFNULL([Event Date], [Interview Date])

               

              That should get you there!  Hope this helps!


              Cheers

              Vinnie

              • 4. Re: Unmerge Second and Third Join
                Matt Youngberg

                Hey Ken,

                 

                Thanks for going to all that work to recreate the data.

                 

                Unfortunately, the excel files that I'm using aren't sheets on the same file, and so I can't create a union between them (according to my understanding). They are all separate files from one other because I have programs running to publish new entries to them every hour.

                 

                Sorry about the Box issue-- that's where I serve my files from to use on Tableau so they're updating with Tableau Server. I've recreated the workbook without the Box connection, so you should be able to use them now. I'll update the original post with the new packaged workbook.

                • 5. Re: Unmerge Second and Third Join
                  Matt Youngberg

                  Hey Vinnie,

                   

                  My spreadsheets are all on different excel files, so as far as I know I cannot union them. Tableau doesn't allow me any type of union when I attempt to in my workbook either. Any other suggestions? Thanks so much!

                  • 6. Re: Unmerge Second and Third Join
                    Vinnie Ahuja

                    This may be a bit convoluted, but may work. Sorry on an older version of Tableau and couldn't open your file, so just explaining below.

                     

                    1) outer join one of the data sources to the date scaffold (I will assume Interview just for illustration)

                    2) outer join the Event source to the Interview source on Event ID = Interview ID.  Assuming these are unique fields and will never be the same, you will get no common records and thus a row for each Event and each Interview.

                    3) duplicate your date scaffold source and outer join it to the interview source.

                    4) create a "master date" field in Tableau using the following calculation (I will assume the date in your date scaffold is called "Cal Dt")  ifnull([Cal Dt from interview], [Cal Dt from Event])

                     

                    You should then be able to produce the following.  Note you may have to add a unique row identifier like INDEX() to make it present cleaner.

                     

                    • 7. Re: Unmerge Second and Third Join
                      Ken Flerlage

                      Have you considered using Tableau Prep to union them together? If that's not an option, then we can do some crazy join work. All my sheets are still in a single workbook, but this should work with different workbooks as well.

                       

                      I'll start out by adding one additional data source I'll call "Duplicate" which will allow us to duplicate our data so we can do some joins to each of the tables. The data just has a single column called "Dupe" with two rows of values 1 and 2.

                       

                      Then, in Tableau we'll add Dates and Duplicate to the data pane and join them using join calculations of 1=1. This will essentially duplicate each date with values 1 and 2.

                      Then add in the Events table and join as follows:

                      Then do the same thing with Interviews:

                      In the end, your data will look like this:

                      You'll have some extra NULL rows, but you could filter those out easily enough. To do that, I'd create a calculated field that just checks for records where each of the four fields are NULL and Dupe is 2. You'll still have the NULL values with Dupe of 1, so that will allow you to have rows for every date.

                       

                      See attached workbook.

                      • 8. Re: Unmerge Second and Third Join
                        Matt Youngberg

                        Hey Vinnie,

                         

                        I'm sure that would have worked, but the software that I'm pulling the events and interviews from do not have unique IDs between the two. Otherwise, it would've worked, I'm sure!

                         

                        Thanks so much for the help. I resolved my issue. I appreciate the time you put into it.

                        • 9. Re: Unmerge Second and Third Join
                          Matt Youngberg

                          Hey Ken,

                           

                          This worked for me! Thanks so much for the answer!