I'm not familiar with multi-assign attributes, but from the information in your link I think the data prep features in Tableau Desktop would be able to handle it.
The two functions I have in mind are the Split function and the Pivot function. Both are accessible from the data connection screen:
The splitting would be needed if the attributes are imported as a single delimited field. The pivot will help to drop all of the like values into a single column. From the example data in the link, the resulting table would have two columns: Movie Title, and Actor. This will put the data in a format most easily used for visual analysis with Tableau Desktop.
Thanks for your reply. However, Split & Pivot are not doing exactly what I want.
I would have liked to be able to do some data discovery quickly (quick and dirty), meaning that avoiding having to go through complex data cleansing and transformations, while being able to understand the data would have been a great feature of Tableau. Or maybe this exists, but I'm just unaware of it ...
Let's assume that I have some raw data like this.
CompanyName Industries Employees Company1 Manufacturing, General Business, Telecommunication, Healthcare 12342 Company3 High Tech, Healthcare 86 Company2 Financial Services, Manufacturing, Media Retail 1000
I would like to be able (with minimum amount of work) to determine the workforce potentially available across all listed companies for each industry on which those companies have or had projects.
So, something like this:
Manufacturing 13342 General Business 12342 Telecommunication 12342 Healthcare 12428 High Tech 86 Financial Services 1000 Media Retail 1000
This is OOTB available when going via Endeca. So, do I have any means of doing it also using Tableau?
Thank you once again!
You are correct in saying that the Split & Pivot doesn't give you what you want. For now, Tableau only supports one or the other, but not split and then pivot. Hopefully we'll see that in future versions (you can vote up this idea: http://community.tableau.com/ideas/4477 to voice your support for the feature).
In the meantime, I think you are unfortunately stuck with working on the data in the back-end or using some kind of custom SQL to pivot the table. If you know the upper limit to how many industries can make up the comma separated list, then you could try a work-around like this (admittedly tedious):
- Perform a cross join to an N record table (where N is the upper limit) which has a column giving you values 1 through N. Your data set will look like:
N Company Name Industries Employees 1 Company1 Manufacturing, General Business, Telecommunication, Healthcare 12342 2 Company1 Manufacturing, General Business, Telecommunication, Healthcare 12342 3 Company1 Manufacturing, General Business, Telecommunication, Healthcare 12342 4 Company1 Manufacturing, General Business, Telecommunication, Healthcare 12342 1 Company3 High Tech, Healthcare 86 2 Company3 High Tech, Healthcare 86 3 Company3 High Tech, Healthcare 86 4 Company3 High Tech, Healthcare 86 1 Company2 Financial Services, Manufacturing, Media Retail 1000 2 Company2 Financial Services, Manufacturing, Media Retail 1000 3 Company2 Financial Services, Manufacturing, Media Retail 1000 4 Company2 Financial Services, Manufacturing, Media Retail 1000
- Create a series of calculated fields in Tableau to capture a split for each position (up to N). Unfortunately, the Split function requires a literal integer, so we can't just use N as an argument:
TRIM( SPLIT( [Industries], ",", 1 ) )
TRIM( SPLIT( [Industries], ",", 2 ) )
... and so on.
- Then one final calculated field to select the right split field based on N:
CASE [N] WHEN 1 THEN [Industries - Split 1] WHEN 2 THEN [Industries - Split 2] WHEN 3 THEN [Industries - Split 3] When 4 THEN [Industries - Split 4] END
- Now, you can create the view you want:
And filter out the blank Industry values to get:
If you are creating an extract, I would recommend adding the exclusion of blank Industry as an extract filter.
Admittedly a lot more work than you'd probably like, and hopefully something that will be OOTB in the future, but at least it can be done!
multi-assign.twbx 44.6 KB
Thanks for your very detailed answer!
It is indeed not what I was hoping for, but it's painting at least the idea of what one could do when confronted with such challenge (in Tableau 9).
I have voted for supporting the push for an OOTB (transparent) implementation, as this approach would not be feasible anymore, if having to deal with multiple multi-assign attributes at once (again as Oracle EID is currently capable of).