The Codebook - Part 1

What is a code book and why do I need one?

The code book contains all the important instructions and criteria that should be used to link the data to DataLion. This defines the standard settings of a project and serves as a guide in a broader sense.

Examples of default settings:

• Navigation structure (several variables can be bundled, for example, to increase clarity)
• Summary of variables in a chart
• Variables and values (value field)
• Captions (e.g. question text and values)
• Chart type

With the help of the code book data can be visualized quickly and easily. The standard settings can be flexibly changed at any time via the administration area of the dashboard.

Structure of the code book:

The following columns should be created:

Row_ID

The row ID is numbered consecutively. A row ID must not be assigned twice. DataLion refers to the row ID when creating the charts. If the row ID is changed in the code book, the drop-down filters, for example, must also be adjusted.

Question_ID

The question ID is also numbered consecutively. Rows that have the same question ID will be combined in the dashboard. As a result, several variables and values are bundled into one chart and displayed as a stacked bar chart, for example.

IMPORTANT:  Row ID and question ID must not be assigned twice.

Variable

Variables designate certain properties or subgroups within a question. The variable must match the question ID. A variable can have multiple values.

Value

The characteristics are specified via the value. With the help of Variable and Value columns the data in DataLion can be linked.

In the case of non-numerical variables, i.e. the value is to be displayed as text, the value <label> is used in the code book. The value <label> is particularly suitable for many text values, for example when brand names are queried.

In addition, new variables can be added via the value in the code book, i.e. recoded or calculated, by using the existing variables in the data set for new values or calculations with the SQL command language. For example, age groups can be formed or scores can be calculated.

A collection of the SQL commands can be found here.

Description

This column is used to label the values and the question text in the diagram in DataLion. The maximum length of the question is limited to 255 characters.

Short description

This column gives the diagram a title and appears in the navigation.

Type

The type indicates whether it is a question, a characteristic or a value. This means that every value requires a question. Simple questions consist of a question and values. Stacked questions contain all three types, i.e. question, characteristics and values.

Chart-Type

The chart type defines the chart in which the data is to be displayed. If you know in advance how the results should be visualized (e.g. bar or pie chart), you can select the appropriate diagram type here. However, the chart type can always be changed afterwards via the administrator area. Here you will find an overview of the chart types available and give tips on which chart type is suitable for certain data.

Level

The level defines the navigation structure. It is also possible to create several levels.

Special columns that can be created optionally:

It is also possible to create special columns in the code book, which can be created optionally.

"Numerical" column (order), for example, can specify the order of the values in the code book. If the order is not defined, DataLion arranges the values one after the other according to the row ID. Sorting is determined by the "numeric" column.

• The "Numerical" column arranges the values consecutively according to the specified numbering (1,2, 3, ...).

"Sort order" column

In addition, the sorting order (e.g. ascending or descending) can also be changed in the chart settings. If a value should always be placed at the end of the sorting (e.g. "Other"), this is made possible by an additional column.

• Using the value "1" in the "Sorting order" column, one value will be never or always positioned last; the value "0" is used for all other values that remain sortable.

IMPORTANT: When uploading the code book, column must be linked to the "Variable Display" column.

„Position_id“ column

In the code book, the navigation menu can also be specified using the "position_id" column, i.e. which variables are shown and hidden.

IMPORTANT: When uploading the code book, the new columns must be linked to the "Variable Display" column.

"Settings" column (color scheme)

An individual color scheme can also be configured for each question/measure in the code book.

e.g.: {"colorscheme":{"scheme":["#ffffff,#dddddd"]}}

IMPORTANT: When uploading the codebook, the new columns must be linked to the "Settings" column.

Tip: DataLion can only visualize columns that have also been linked. This should be considered in particular when variables are renamed.

How can multiple answers be displayed in one chart?

Multiple answers can be bundled into one chart in the codebook. This works via the Question_ID column. Variables containing the same question ID are bundled into one chart.

Example:

How can stacked bar charts be displayed in one chart?

Multiple stacked bar charts can also be displayed in one chart in the codebook. The chart type "multistack" is used for this and each characteristic is created one after the other. The assignment to a chart works via the Question_ID column. Characteristics that contain the same question ID are bundled into one chart.

Example:

THEN paragraph ELSE 0 end) - SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 24 month) FROM dashboard_data_table_15_gfk) AND period < (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_15_gfk) THEN paragraph ELSE 0 end)) / SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_15_gfk) THEN paragraph ELSE 0 end)

Here we continue with part 2. In this section we will show you how to perform calculations and recoding.