The error you get from running your R script is generated
because the STRSPLIT() function returns a list as a result,
but Tableau is expecting a vector as a result.
So formally one need to transform a list to a vector first,
then return it back in Tableau.
But there would be another complication because of that.
Tableau is expected the resulting vector(s) to be the same size(s)
as the addressing window(s) in the initiator Table Calculation
(this exact pill on a view which calculates the script formula).
So in your particular case it would be one vector of the size of 7,
or even the two vectors of the sizes 3 and 4 (if Compute using -> Cell).
To cope with this, one would have to generate somehow
an additional Marks on a view -- up to a total of 7 in your case --
from the two original Dimension Values.
So the (seemingly simple) problem quickly becomes complicated.
A common approach is to transform your data prior to Tableau --
using a tool which does a Split & (Un)Pivot transformation(s).
Besides, Tableau has both ones -- the SPLIT() function and the Pivot option --
but the order of execution can not be 'Split first then Pivot'
(in fact it could be in reverse, because of the internal Order of Operations).
Another approach -- which helps in a particular case of finite word counts --
is to generate the 'Scaffold' rows for the Marks right in the datasource --
typically using a Join with a sequence of integers (as a table of course).
Then use Tableau (Row-Level) string calculations to fill those Marks on a view.
Please find the attached as an example of the latter.
Hope it could help a bit.
N_Words_v10.2_YF.twbx 1.2 MB
Thanks so much for the detailed reply - the final portion and attached workbook are the exact solution we're looking for.
The only problem is, I see on your join you used a Join Calculation of '1' to join them. However, our client uses version 10.1 and this functionality is not available until 10.2.
Is there an alternative way to join the ID table, or will the attached solution only work for us in 10.2 and greater?
Glad to help.
The old-school (pre-10.2) method of a cross-join
is to have a column in each table with the value of 1
and join on it.
I think we will be able to do that. The only issue with the above solution is that it creates duplicate counts and skews the overall numbers. For that reason I'm creating a second connection to the same data source, for this individual view. That way it doesn't inflate any numbers in the base data.
Let me know if you have any other advice on that, but otherwise thanks very much - very helpful!
James, you're welcome.
My ultimate advice:
If it works, let it work.