Susan et al,
Some folks at Tableau prompted me to see if a blend would solve the problem. I had in fact tried this before and ran into problems but the problems where unexpected results from the Tableau data interpreter and not accounting for how the pivot feature handles nulls.
In any case, it appears that for many things you can get the grouping and aliasing you need with a blend. I still very much want to do a join so I can combine text responses with numeric responses, but for the aliasing it appears that a blend will do the trick.
I'm happy to walk you through the process.
1 of 1 people found this helpful
I'm going to go through some points of clarification re: terminology & functionality here, because your statements mix up the common terminology used for joins and blends and give an unclear picture of what is available in Tableau v9.
join = what happens when we connect tables (or database views, etc.) in the data source window:
The result of a join is a data source in Tableau.
blend = what happens when we connect different data sources:
The primary data source is the first data source used to build the worksheet and gets the blue background check mark. The secondary data source(s) are ones blended to the primary and get the orange check marks.
This is all true for Tableau since data blending was introduced.
The new pivot feature in version 9 lets us pivot a set of columns to be more rows in the data. The pivot occurs *after* the join, not before. Here's an example where I've got one table with columns for 2001-2003 and a second table with columns for 2004-5, after the tables are joined on the common key (Row ID in this case) I can do a pivot across the fields from the two tables:
And the result of the pivot:
Ok, so in v9 the pivot happens *after* the join. That means that we can't do things like pivot a set of survey questions that have things like Q0183731 for column headings and immediately join in human-readable question names. For short, I call this "pivot and join", where we want to pivot one set of data and then join the pivoted data to another set of data.
However, data blending happens *after* the pivot (and some other things like aggregation, alias assignment and densification, I'll be doing a Think Data Thursday on this in a couple of weeks, see http://community.tableau.com/thread/157773 for details). So we *can* use a pivot to build a data source and then do a data blend to a secondary source to get the effect of a pivot and join. Here's a case where I did just that -- the QUESTION dimension is a set of pivoted columns that are blending to the secondary data source that has the Description:
And from there, we can then use the Primary Groups feature to "push" the Descriptions to create a group in the Primary source and therefore no longer require the secondary source:
So in Tableau v9 there are three different solutions for this situation of wanting to pivot and join:
1) Do all the work in the original data source to do the pivot & join and have a single Tableau data source.
2) Do the pivot in the original data source, make that available as a table/view/Custom SQL query in Tableau, then join the additional data in Tableau to have a single Tableau data source.
3) Do the pivot in Tableau, then connect to the additional data as a second data source and use a Tableau data blend to connect the two sources (and potentially use Tableau's Primary Groups to end up with only a single data source with all necessary information to build the view).
Does that make sense?
Thank you Jonathan for your wonderful post! I have updated the original to make sure folks (like me!) looking for help on this topic find your information. I'm sure there are many others who will struggle with this based on how we were doing mass aliasing prior to 9.0.
Your engagement here is very much appreciated!
The primary group approach is a nice workaround.
You just have to remember to update the primary groups after any change to the question descriptions. And hope that anyone editing the workbook also remembers.
OK Jim, that's something that I definitely would have failed to recognize (and, therefore, would have screwed up). Thank you.
I responded to the other thread already but wanted to chime in about the Create Primary Group feature.
I did not know this even existed. Great stuff.
I still want / need a join so I can have both text and numeric survey responses available to me, but there are ways to work around that (albeit somewhat cumbersome).
Thanks for pointing this out, Jim! Requiring manual intervention for
updates is the biggest reason why I rarely use primary groups.
On Thu, Mar 12, 2015 at 7:08 AM, Jim Wahl <
I'm wondering what you mean by "...both text and numeric survey
responses..."? Are you referring to the Pivot Field Values stuffing all
responses into a single column?
On Thu, Mar 12, 2015 at 10:30 AM, Steve Wexler <
When one downloads survey data using a tool such as Qulatrics, the tool give you the option to download the responses as text (e.g., "strongly disagree", "disagree", "neutral", etc.) or as numbers (e.g., 0, 1, 2, etc.).
I find that life is much easier with both options. So there will be three columns
Question ID Value Text
I'll reshape twice and then join. Works great.
Steve I'd love to understand how you're doing this Value/Text thing.
What I want to do is take this piece...
... and this piece...
and make something that looks like this:
By having both label and numeric responses I avoid having to deal with creating many CASE statements in Tableau.
CASE [Value] WHEN0 then "Not at all"
WHEN 1 then "Small degree"
WHEN 2 then...
You'll have to do this over and over again as sometimes a 0 will mean "Not at all" and at other times it will be "strongly disagree".
The same problem occurs if you attempt to alias values. If you turn the values unto strings you can only alias each string 1 time, so you end up having to create duplicate fields.
If you start with text values, then you have to create lots of versions of this:
If [Response] = "Not at all" then 0
ELSEIF [Response] = "Rarely" then 1
I'm posting a video of my session from the 2014 Tableau Conference (you can find it at Visualizing Survey Data using Tableau - YouTube). The applicable part may be found at around 14:00 into the presentation.
Interesting. I think I'm doing what you're trying to avoid, but more manually in my data source (Excel) before I reshape and connect in Tableau. I'll check out the YouTube.
Thanks again for your generosity in helping me out!
I have binary columns from a survey. The values in each column are either 1 (representing Yes) or -1 (representing No). The columns belong to two categories: Applications and Hardware. How do I do the join/blending in a way that gets me two quick filters (Applications, Hardware) without nulls?
I'm trying to do Pivot survey data that is stored in an Access db. However, the Pivot option in the drop down is not available when connected to Access.
Is it possible, or only with flat files?