For this example, the Call table is in a SQL Server Database. The Agent/Supervisor data is in an Access table. We will also have some data with date ranges in Excel.
Thanks. Any help will be greatly appreciated.
You can join these tables using what's known as a blend in Tableau. The following links provide more information on data blending:
Hope this helps!
Tracy, I don't believe a data blend will work in this case because the join from the call data to get the supervisor columns needs to look between the start date/stop date of the supervisor(s) for that agent, and data blends don't support that. How were you thinking it would work?
Given that assumption, I'd approach this by:
1) Creating a federated data source. You can do this in Excel, Access, or SQL Server by creating linked tables that point to the original data source.
2) Create a SELECT query (in Tableau Custom SQL, Access, or SQL Server) that starts with the calls data and then has two joins on the supervisor table, one to get the supervisor for the date, the other to get the current supervisor, using a WHERE clause like this for each: WHERE [Call].[Agent] = [Supervisor].[Agent] AND ([Call].[Date] >= [Supervisor].[Start Date] AND [Call].Date] <= [Supervisor].[End Date])
3) Use that query as the data source in Tableau.
That way no update queries are necessary and Tableau's data source will have live data.
Ah yes, I looked too quickly through the data
I thought the blend could be made on the Agent--but they're not unique for each row.
Elaine, Jonathan's approach is better in this scenario. But at least now you know about data blending for future projects.
Thank you both for your replies! Greatly appreciated.
I saw some information about blending and I wondered how to do that when I was combining a date with a date range. Not sure how that would work either.
For the Custom Sql, there problem is that the call data is in SQL but the supervisor data is in Access. In Qlikview I could bring the two data sources into tables in a script and then combine them into one amd drop the orignal tables. Is that possible in Tableau? We may have to look into getting all the date range files into the SQL database so we could do the Custom SQL.
That particular feature of Qlikview (scripting to create a unified QV data source from data sources residing on separate servers/applications) is something that Tableau can't do at this time (I'm hoping a future release will enable this). That's why I suggested creating the federated data source. You could still leave the original tables in their original source, you'd just create linked tables in either SQL Server or Access that Tableau could then see in the data connection to the SQL Server or Access table. For example, here's how to set up a linked table in Access that points to SQL Server: http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx. Here are instructions on linking a server from SQL Server: http://msdn.microsoft.com/en-us/library/ff772782.aspx.
Thanks so much for your quick reply.
The company is trying to get us to move away from Access so I'll see what I can wth SQL.