1 Reply Latest reply on Apr 25, 2017 2:19 PM by Patrick A. Van Der Hyde

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

    Tejaswini UL

      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!