The Deliverables applet provides users with several different file types for exporting reports, including Verticalized (Tableau) exports. Verticalized (Tableau) reports display responses in a "verticalized" (vertical orientation) output with a single column for a question and multiple rows for each respondent record. Only some field types (e.g., multiple select questions and variables, tables, blocks, loops, and ranks) are verticalized and exported in the Verticalized File. Two additional files can be exported to pull the fields that are not considered verticalized, the Non-Verticalized File and the Non-Verticalized File with Codes. Additionally, single select fields can be appended to the verticalized export in the Export Options section.
For more information on creating exports in general, see Creating a new export.
Understanding the concept
In general terms, "verticalized" data is data that is presented in a vertical orientation where the survey question/variable is displayed in a single column in a report. Multiple rows exist for the question, where each row represents a new element related to the question. For example, a multiple select (checkbox) question with five answer choices would be presented in a single column with five rows, one row for each option. Verticalized data, then, expands by adding more rows until all the data is represented within the single column. This is the opposite of the more common approach of displaying a single survey question across multiple columns, e.g., "non-verticalized" data. This data is displayed horizontally and expands by adding columns to the right for each additional element or related data.
In IntelliSurvey's software, the term "verticalizing" means to take a survey question that would normally be displayed in multiple columns and instead display it in a single column. For example, a single select (radio) table asking about "likelihood to recommend" for three brands would normally be displayed with one row and three columns, where the single row represents the respondent, and each column represents one of the three brands. Meanwhile, in a verticalized report, the same table is represented by one column and three rows, where the column represents the table, and each row represents one of the three brands.
Since single select questions are displayed in a single column by default, the option to verticalize is not applicable. Therefore, verticalizing data is not simply "all the data appears vertical." Rather, it is the action of changing the data format from horizontal to vertical, where relevant.
Verticalized (Tableau) output for radio table TS32
Non-verticalized standard output for radio table TS32
The Verticalized (Tableau) export described in this article differentiates between two default categories for common question types: verticalized and non-verticalized. A good principle to follow is, if a single survey field generates multiple columns in a regular Excel export, there is a high probability this question type can be reformatted vertically and is considered verticalized. Conversely, if this question type is already in a single column, then there is a high probability this field is considered non-verticalized.
Creating a verticalized export
To create a Verticalized (Tableau) export, click on Deliverables under the Reports button group in the Navigation menu. Next, click on the New Export option in the menu bar to open the Create Exports screen. In the Export Options section, select Verticalized (Tableau) from the File Type dropdown menu. Use the Field Selector to select which field types to include as the columns and the Record Selector to select which respondents to include. Verticalized reports default to using All Questions and Record Data and Completed records, respectively.
Note: Single select questions and variables appear in the Field Selector. However, these fields do not appear in the Verticalized File. Instead these fields appear in the Non-Verticalized File. If a single select question or variable is needed in the Verticalized File, select it in the Append fields to verticalized records selector instead.
Note: Like other reporting applets, Verticalized (Tableau) reports may be created for "Dev" (in development) or "Pub" (published) versions of the survey by using the Dev/Pub toggle in the applet header. If the most recent version of the survey has been published, the toggle will be inactive. For more on Dev/Pub, see Dev and Pub Mode.
Users can also elect to Append fields to verticalized records, which allows the report to include additional fields such as single select questions that are typically considered non-verticalized and omitted. This is particularly useful for adding demographic questions so they can be viewed along with the verticalized results for filtering data. Users may add these fields via the pulldown menu or by typing in the text box. Note this field is only available for verticalized exports. These fields are added at "the end" (to the far right) of all other verticalized fields/columns.
Once the desired fields and records have been included, users should indicate which files to create in the Verticalized (Tableau) Options section. By default, all file types are selected under the Include Files field, and the Equalize open-text option is selected as well. The file types reflect the data categories verticalized and non-verticalized as described at the beginning of this article. Additionally, there are some question types that are in neither category and are not exported. The question types are grouped as follows:
- Verticalized – checkbox (multi-select) questions/variables/quotas, checkbox tables, radio (single-select) tables, numeric text tables, content iterated within blocks/loops (including those with radio questions), and rank questions
- Non-verticalized – single select questions/variables/quotas, standalone numeric text questions, MaxDiff variables
- Neither – non-numeric text field/text box questions, non-numeric text field/text box tables, and uszip5 datatypes.
Tip: Questions may be re-designated for verticalized or non-verticalized outputs by applying the tags export as verticalized
and/or export as non-verticalized
to their code, which accept 'y' (yes) and 'n' (no) inputs. These tags can be used in concert to shift a question from one category to the other, or individually to either remove the question from a report or include it in both.
If need be, additional export options may be included in the Advanced Export Options portion of the screen. By default the Compress data file and Expand stacked fields options are selected.
Once all export options have been selected, click Next to continue.
This will bring users to the final Create Exports screen. If the file is successfully created, users will have the option to download the file, save the export, or both. Note that the Save Export button will not be active unless a file name is entered in the Description field. If need be, users may alter the report options via the Adjust file button at the bottom of the screen.
Once the export has been downloaded and/or saved, users may elect to create additional exports via the Create another Export or Adjust file buttons. To return to the Deliverables home screen click either Done or the Deliverables button in the menu bar.
Verticalized (Tableau) output
Verticalized (Tableau) exports are outputted as .csv files. If the default Include Files settings are maintained, three separate files will be downloaded in a zip file – a verticalized file, a non-verticalized file, and a non-verticalized file with codes. Each file looks slightly different.
Report layout
Below is an example of a typical Verticalized (Tableau) export file.
The following columns are added by default in all verticalized export files.
Column | Description |
---|---|
id | The respondent ID number. |
VERT_Q_ROOT | The base question ID. |
VERT_Q_ROOT_TEXT | The base question's text. |
VERT_Q_NUM | The sub-question ID. In the case of checkbox questions, this may be listed as QX.1, indicating option 1 for QX; with tables, this may be listed as QXR1, indicating row 1 for TX. |
VERT_Q_TEXT | The text for the sub-question associated with the VERT_Q_NUM. |
VERT_RESP_CODE | The code value associated with the respondent's selection. |
VERT_RESP_TEXT |
The text version of the VERT_RESP_CODE. |
Respondent IDs are listed vertically, with questions and survey fields listed as the rows. The "base question" is listed in the VERT_Q_ROOT column, with any "sub-questions," such as the individual options in a checkbox question or the row-questions in a table, as separate rows in the VERT_Q_NUM column. The respondent's answers are then displayed in both text and closed-end values. Lastly, any non-verticalized fields appended to the report will appear to the far right, at the end of the file.
Take the example above. The checkbox question Q1 has eight options in the survey, with each option displayed in its own row. Target, Q1 option 1, has a value of '0' because it was not selected. Walmart, Q1 option 2, has a value of '1' because it was selected. The appended data to the right simply repeats the answers for QD1 as many times as needed for the respondent.
Altering verticalized or non-verticalized categorization
Starting in r8.0, Survey Programmers (SPs) may add tags to a survey field to alter whether it is categorized as verticalized or non-verticalized. The tags
export as verticalized
and export as non-verticalized
may be used individually or in concert to switch a question's classification so that it can be included with fields outside of its typical designation. Both accept the inputs 'y' (yes) and 'n' (no).
Note: If a question's default categorization has been altered, check all files before sending them to a client to ensure the data appears as it should.
Syntax
export as verticalized: y/n export as non-verticalized: y/n
Example
1. Which of the following activities did you do on your last vacation? type: checkbox export as verticalized: n export as non-verticalized: y 1. Going to a museum 2. Taking a city tour 3. Visiting a historical site 4. Attending a local sporting event 5. Visiting a brewery or distillery 99. Not interested in any of these {exclusive: y}
Suppose we have screener question Q1 that asks which activities the respondent did on their last vacation. This question is a multi-select question, and by default would be exported in the verticalized file. However, the client wishes to have all the screener questions removed from the verticalized file and appended as non-verticalized so they can easily filter the main survey data on all screening questions.
To have Q1 included in the non-verticalized export files, use the tag export as verticalized: n
and export as non-verticalized: y
. Adding both tags in this way ensures that Q1 is both excluded from the verticalized file, where it typically would have been, and included in the non-verticalized files. In this example, if only export as non-verticalized: y
is used, the default to export in the verticalized file would remain intact, and Q1 would then appear in both files.
Comments
0 comments
Please sign in to leave a comment.