You may need to do a left/right outer join. Please check.
If you are not sure, please attach the packaged workbook (twbx) so that someone can look into it and suggest the appropriate solution.
I tried to work with the 3 extracts you provided, and couldn't figure out the joins exactly. Also, I would probably prefer the original files instead of these extract. If you can't provide them, then what you should do to resolve this is, take list of all questions and then the survey responses from one user and then set the joins accordingly.
I'm with Hari's response as well. I opened up all 3 .tde's and the Helper File appears to be the issue. So perhaps best to go back to the original files if possible and provide those?
I can join the Labels and Numbers files appropriately by ID, but because work was already done on those data prior in Tableau Prep it's difficult to tell what else might be contributing to the issue.
So far though, I can't see anything common in terms of uniqueness between the Helper File and the other two files...there needs to be some unique field to join to and nothing is lining up.
E.g., the Grouping column data should line up with something in either the Labels or Numbers data, but does not; or, the QuestionId data should line up with something in either of those other tables, and while it looks like they could based on some similar spelling, does not do so due to different syntax.
Thanks very much for looking at this. The slightly different names
(Question ID in the Numbers and Labels files and Questionid in the helper
file) were intentional. I originally had the same question id name in all
3 files but kept getting a message from Tableau that it was not possible to
join the helper file with the combined numbers/labels file on that variable
if they had the same name. Also, the instructions I was following ( at How
to prep your survey data using only Tableau 10 and beyond | Tableau Software
) used different names for question id in that last step (joining the
helper file with the joined numbers/labels file) in order to avoid this (or
so I thought). I will have to look into this further.
When I do follow the instructions exactly (matching the helper file and the
two other files on Question ID and Quesionid), a subset of survey questions
do match/join correctly, while the majority of questions don't appear in
the resulting file. While I copied and pasted question ids in the various
files in order to have identical question names, I will also check to see
if the question ids differ in some way for the questions that don't join.
Maybe there's an extra space or something?
I am attaching the excel files with the data here.
One thing to point out perhaps is that there is a lot of "missing" data in
the files because the survey had a lot of branching and skip logic, so not
everyone viewed or was required to answer every question. Would this have
caused any problem in joining the files?
Thank you again for all of your help.
On Mon, Aug 13, 2018 at 3:48 PM, Don Wise <email@example.com>
Thanks for the data files. Now, I have the solution for you.
Since both the data labels and numbers have the same structure, please put them in 2 separate tabs of a single Excel sheet. You will then be able to Union them as shown below and then pivot your data, followed by a join with the helper file. The primary reason why the direct join was failing is because when we pivot the data, Tableau is removing the underscores ("_") and so you were losing most of the data. That would need to be resolved by a calculated join as shown above.
I have attached the workbook. Hope this helps.
Survey Data.twbx 1.0 MB
Hari, I figured you'd take the lead ! Great job as always! (PS found same results as you just prior to your post) Don
Thanks Don. Glad that we both have the same results.
Thank you so much, Hari. I am very grateful to you.
Is it advisable not to use underscores in variable names in Tableau?
Thank you again!
1 of 1 people found this helpful
You are welcome Susan. I wouldn't say that it's not advisable, since we may use them to stick to naming conventions that we follow. And Tableau anyway does allow us to create a calculated join, so it shouldn't matter.
Since your issue has now been resolved, please close this thread.
I marked my original question as "Answered." Does that close the thread?
Yes, thank you.