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?
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.
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!
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.
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!
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.
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.
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.
This worked for me! Thanks so much for the answer!