Alternative table

An alternative is one of the answer options the survey taker can choose between for some question in the survey. It could also be the possible values for other enumerated fields in the survey table, that may have originated in a feed file from the client, or even been generated by the system. When running the Metadata query on the survey table (DESCRIBE TABLE survey), the output includes columns that inform for each field if it is enumerated and what the alternativesetid is. So for any field that has encoding=enumerated, it is possible to run a query towards the alternative table for the corresponding alternativesetid to obtain the possible values that this field can take.


Column name	Data type	Description
alternativeset	integer	The identifier of the alternativeset this alternative belongs to. To see which alternativeset a given field uses, run the Metadata query.
sequence_number	integer	A unique identifier for the alternative (unique within the alternativeset).
name	string	The name of the alternative as it should be shown to a user.
shortname	string	The name of the alternative as it should be shown to a user in a space-constrained context.
numericvalue	integer	The integer value of this alternative, used in calculations. An alternative may not necessarily have a numeric value, in which case it would not count when doing aggregations.
boxcolor	string	A HTML hex color value to be used to visually indicate to the user if this score is good or bad (typically 9-10 on a 1-10 scale would be green, while red or black is used to indicate low scores).
conditionname	string	he name of the alternative for use in a WHERE condition of an MQL query.

Table description

DESCRIBE TABLE alternative

 key             | name            | encoding |
 alternativeset  | Alternativeset  | TEXT     |
 sequence_number | Sequence number | TEXT     |
 name            | Name            | TEXT     |
 shortname       | Short name      | TEXT     |
 numericvalue    | Numeric value   | TEXT     |
 boxcolor        | Box color       | TEXT     |
 conditionname   | Condition name  | TEXT     |

Sample query

The query must contain a where condition on the alternativesetid:

SELECT alternativeset, sequence_number, name, shortname, numericvalue, boxcolor FROM alternative WHERE alternativeset=14

Result:

 alternativeset | sequence_number | name             | shortname        | numericvalue | boxcolor |
 14             | 1               | 1                | 1                | 1            | #333333  |
 14             | 2               | 2                | 2                | 2            | #333333  |
 14             | 3               | 3                | 3                | 3            | #d60000  |
 14             | 4               | 4                | 4                | 4            | #d60000  |
 14             | 5               | 5                | 5                | 5            | #ffcc00  |
 14             | 6               | 6                | 6                | 6            | #ffcc00  |
 14             | 7               | 7                | 7                | 7            | #99cc00  |
 14             | 8               | 8                | 8                | 8            | #99cc00  |
 14             | 9               | 9                | 9                | 9            | #008000  |
 14             | 10              | 10               | 10               | 10           | #008000  |
 14             | 11              | N/A              | N/A              |              |          |
 14             | 12              | Not asked        | Not asked        |              |          |
 14             | 13              | Not answered     | Not answered     |              |          |
 14             | 14              | Multiple answers | Multiple answers |              |          |