Vars to cases retain variable value labels Tableau setup - restructure data for Tableau flip data

0 votes

I am flipping my survey data so I can use it in Tableau. Here is example data in SPSS (keep in mind that each variable has value & variable labels).

ID    age   rate1   rate2   rate3   mr_1    mr_2    mr_3     ...
1      35    8        3       2      1       2        
2      40    2        2       3              2       
3      41    6        3       5              2       3
4      43    3        3       1         

Where rate1-3 are 3 rating questions. Mr_1 to mr_3 is a multiple response check all the apply question (What is your ethnicity? 1=White 2=Hispanic, 3=Black)

I flip the data using this:

VARSTOCASES
  /MAKE answer FROM age rate1 rate2 rate3 mr_1 mr_2 mr_3
  /INDEX=Index1(7)
  /KEEP= All
  /NULL=KEEP.

Results look like this:

ID Index1 answer
1    1      35
1    2      8
1    3      3
1    4      2
1    5      1
...
...
...

Which works just fine when connecting this to Tableau. However, what I want is more than just Index1 as an identifier to each variable that has been flipped. What I want is this (Var, VarLab, ValueLabel are just String variables):

ID   Var           VarLab            answer    ValueLabel
1    'age'      'What is your age?'      35         '35'                             
1    'rate1'    'Rate food'               8         '8'
1    'rate2'    'Rate wait time'          3         '3'
1    'rate3'    'Rate bathroom'           2         '2'
1    'mr_1'     'Ethnicity'               1         'White'
1    'mr_2'     'Ethnicity'               2         'Hispanic'
...
...  
...    

As you can see, I retained the variable label, value label, and the variable name itself for each flipped variable. This is the ideal Tableau setup as Tableau requires "tall" datasets. Also, I can use either the string or numeric representation of the response. Lastly, I no longer need to edit aliases inside of Tableau. Any ideas how to accomplish this? Perhaps this will require python or macro? Any ideas are greatly appreciated.

Thanks!

Jun 5, 2018 in Tableau by ghost
• 1,790 points
1,659 views

1 answer to this question.

0 votes

you need to use OMS to read the dictionary into two datasets - one for variable labels and one for value labels. then you can match your restructured dataset to the variable labels by variable name, and then match it to the value labels by variable name and value.

Run this to get the two datasets - BEFORE you restructure, of course:

DATASET DECLARE  varlab.
OMS   /SELECT TABLES   /IF COMMANDS=['File Information'] SUBTYPES=['Variable Information']
  /DESTINATION FORMAT=SAV OUTFILE='varlab' VIEWER=YES.
DATASET DECLARE  vallab.
OMS  /SELECT TABLES   /IF COMMANDS=['File Information'] SUBTYPES=['Variable Values']
  /DESTINATION FORMAT=SAV OUTFILE='vallab' VIEWER=YES.
display dictionary.
omsend.

now restructure and match files - (after renaming the proper variables for matching in the two new datasets).

answered Jun 5, 2018 by Atul
• 10,240 points
Super helpful, thanks!
Hey! I hope you are doing good!

If the answer has helped you, please do upvote it.

Related Questions In Tableau

0 votes
1 answer

How to use tableau to searh for text within entire data

You can use a pivot table in ...READ MORE

answered Apr 4, 2018 in Tableau by xyz
• 1,560 points
835 views
0 votes
1 answer

How to dynamically determine and categorize duplicate value in Tableau?

Apparently you define a duplicate records as ...READ MORE

answered Apr 13, 2018 in Tableau by xyz
• 1,560 points
7,263 views
0 votes
1 answer

Do I need a license for Tableau Server to publish content from Tableau Desktop to the Server?

According to this setup you do not ...READ MORE

answered Apr 17, 2018 in Tableau by xyz
• 1,560 points
1,094 views
0 votes
1 answer

How to access MYSQL Database from tableau?

Hi Sindhu,  1. Double click on Mysql on the ...READ MORE

answered Mar 19, 2019 in Tableau by Cherukuri
• 33,030 points
3,158 views
0 votes
1 answer

How to access MariaDB Database from tableau?

Hi Nithin,  1. Click on More and then ...READ MORE

answered Mar 19, 2019 in Tableau by Cherukuri
• 33,030 points
1,635 views
0 votes
1 answer

how to access google Drive files from Tableau?

Hi, 1. Click on google drive from more ...READ MORE

answered Mar 19, 2019 in Tableau by Cherukuri
• 33,030 points
1,861 views
0 votes
1 answer

How to connect to sap hana database from Tableau?

Hi, Follow these steps to connect to data ...READ MORE

answered Mar 22, 2019 in Tableau by Cherukuri
• 33,030 points
3,280 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
9,941 views
0 votes
1 answer

How to create a bar graph for two fields with same value?

I believe you want to have something ...READ MORE

answered May 29, 2018 in Tableau by Atul
• 10,240 points
1,021 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP