1 of 1 people found this helpful
Ivy, what is your data source? Different data source drivers support slightly different flavours of SQL, so exact syntax may depend on whether your data is in a text file, Oracle, SQL Server, etc.
It also depends on the character pattern in your data, and you might have to use regular expressions to reliably pick out what you need, so posting a data sample will help.
With Excel you are limited to JetSQL, which is what Tableau uses to connect to Excel files.
To extract values from text that follows the pattern in your sample, you will need something like
Left([Product]![Parts],InStr([Product]![Parts],",")-1) AS Prod1,
Mid([Product]![Parts],InStr([Product]![Parts],",")+1,InStr([Product]![Parts],";")-InStr([Product]!Parts],",")-1) AS Quant1
--etc. for all possible positions
and this is only for the first two positions, the following positions will become even more complex as you go. This will most likely result in "SQL too complex" error from JetSQL driver.
I would recommend using Excel's Data -> Text to Columns built-in funciton to convert those long strings into a set of columns in one simple step, and then use the newly structured data. You can then unpivot it (convert to what you have in your Parts sheet), if required, using Tableau's free Excel addin.
Thanks a lot, Dimitri
I like your first idea. I guess I will find a way to work around this issue that way.
For the text to column function. It might work for the sample file, but as I will treat the source as a dynamic changing source, it might not be the optimal way to do that.