Basically, you have to to a simple data transformation -- I call it "Split-then-Pivot".
Both Split and Pivot options are available in Tableau -- but not so for a Combo of them.
Split() is a Row-Level calculation, which could be invoked from the interface
(Transform --> Split on any Dimension Pill of a String Type).
In your particular case, one could split the Owner dimension
into three fields using ; (semicolon) as a delimiter.
Pivot is a datasource transformation option in Tableau
which is available only when using file datasources
(Text / Excel / Statistical).
Ideally, if one could do Pivot on Row-Level calculated fields
(such as the ones derived from a Split), then everybody would be happy :-)
But the Pivot option is available only for the fileds materialised in the datasource.
What can we do now, while we're waiting for the Project Maestro?
We could make both steps in sequence (manually, of course) --
Splitting the Owner field, preparing a detailed view (Sheet 7),
materialising the view into a static Clipboard dataset
(via Select All Marks and Copy-Paste) and subsequently
Pivoting (then materialised) Owner - Split N fields after that.
Please find the attached workbook with that steps done.
Hope it could help.
Multiple_Owners_YF.twbx 99.0 KB
Thanks a lot for your quick reply. Good explanations, I already tried with same way but unfortunately my data source is Microsoft SQL server and this is not working.
Do you have any other option to do this.
Any help appreciated.
If you're on MS SQL Server 2016,
you would be using STRING_SPLIT function.
In Tableau it would be a Custom SQL like this:
t.project_number as [Project Number],
f.value as [Owner Split],
FROM your_schema.your_table as t
CROSS APPLY STRING_SPLIT( t.owner, ';' ) as f