I have text contain many word starting with #, I want to to split all of these and use as a dimension, is it possible in tableau? In Qlik Sense I used Subfield to slove this case.
Can anyone help me, thanks in advance.
Examples:
Row1: ⌗breakfast noodle ⌗paypal
Row2: ⌗breakfast sandwich ⌗airpay ⌗now
Row3: ⌗forwork ⌗hsbc license fee
Row4: ⌗airpay ⌗now coffee passio
I want get them as dimensions look like below (in 1 column & not include value after blank space):
⌗breakfast
⌗paypal
⌗forwork
⌗hsbc
⌗airpay
⌗now
Note:
- Number of hashtag will be add-in frequently, not fix in a list.
- Using Tableau Desktop & Prep
- Edit title & add-in some information to make my question more clearly.
Hi, here are some answers:
re: keeping first word of each hashtag - after you do the pivot operation then do one more split on space to get the first word.
re: the data size increasing & complexity. One piece is to filter out any Null rows added through the workaround, the second is that this is changing the data structure from "one record per social media post" to "one record per hashtag" and yes, you're getting more records out of this. You didn't say how many records you have nor what kind of analysis you are trying to do so I'm not sure how much of an issue that really is given how performant Tableau can be.
In any case there are three methods for working with this:
1) Output the "one record per social media post & hashtag" aka pivoted data set and use COUNTD() or Level of Detail expressions when you want to do analysis at the social media post level.
2) Output the pivoted data and output an unpivoted data set and connect them as two different data sources in Tableau Desktop. Use the pivoted data when you need to do the per hashtag analysis and use the unpivoted data for all other analysis, and use cross data source filters and/or filter actions to make it appear to the user that they are working with a single data set. In this use case the measures would be using COUNT() or SUM(Number of Records]).
3) Tableau v2020.2 adds the relationships feature that is a new way of connecting data sets. In this use case you'd still output the pivoted & unpivoted data, with two changes being that 1) the pivoted data would only have the post id & hashtag. Then 2) Instead of connecting as two separate data sources you'd relate them as two logical tables on the post id. Then you could use COUNT() or COUNT(logical table) and Tableau would automatically resolve the level of detail issues (this is part of the functionality of relationships) and you wouldn't have to use cross data source filters or filter actions (because instead of having two Tableau data sources there's just one).
Jonathan
