Skip to main content

Search

matchto and derivefrom

  • Updated

The matchto and derivefrom tags are streamlined implementations of fetch_cell and fetch_row, making data retrieval from spreadsheets more concise and readable. matchto and derivefrom work as a pair. The matchto tag references the spreadsheet, and derivefrom pulls data from specific fields.

P6. Please enter your ZIP code.
type: text
matchto: system.zip_db_dlxbus
datatype: zip5
maxlen: 5
size: 6

STATE. State
type: radio
derivefrom: QP6 statecode {default: 99} {options: n}
optsfrom: system.states
99. No state found

REGION. One of four geographic regions defined by the U.S. Census
type: radio
derivefrom: QP6 regioncode {default: 99} {options: n}
optsfrom: system.rgopts
99. No region found

Tip! If you are using matchto and derivefrom for a ZIP code question to generate variables for standard US Census data, consider using the newer type: uszip5.

Details

  • Use matchto on the question containing the value to check against a sheet, for example, a ZIP code question. Specify the sheet to check the value against, e.g., matchto: system.zip_db_dlxbus.
  • Use derivefrom to populate a variable. The input includes the question ID where the matchto tag is placed and the column label from the "matched" sheet, separated by a space, e.g., derivefrom: QS5X statecode.
  • The respondent's input from the matchto question is matched to the corresponding row in the sheet (e.g., the ZIP code), and the system returns the value from the specified column.
  • Add the default decorator to the derivefrom tag to set a default value if no match is found, e.g., derivefrom: QS5X statecode {default: 99}.
  • derivefrom automatically includes all options present in the referenced sheet's column. To override this behavior, use the options decorator, e.g., derivefrom: QS5X statecode {default: 99} {options: n}.
  • Widgets featuring a derivefrom tag are invisible.
  • derivefrom variables support the quotas: y tag.

Additional examples

Viewing a basic sheet example

In the example below, QP6 references a system spreadsheet named 'system.zip_db_dlxbus'. If a respondent enters the ZIP code '40004', the software checks the 'system.zip_db_dlxbus' sheet for row '40004'. Now, all of the data for row '40004' can be referenced and used in survey logic.

id state statecode region regioncode
40003 Kentucky 17 South 3
40004 Kentucky 17 South 3
40006 Kentucky 17 South 3 
P6. Please enter your ZIP code.
type: text
matchto: system.zip_db_dlxbus
datatype: zip5
maxlen: 5
size: 6

STATE. State
type: radio
derivefrom: QP6 statecode {options: n}
optsfrom: system.states

REGION. One of four geographic regions defined by the U.S. Census
type: radio
derivefrom: QP6 regioncode {options: n}
optsfrom: system.rgopts

At QSTATE, the system pulls the data from the 'statecode' column for row '40004', returning the value '17'. QSTATE then refers to the system sheet 'system.states' as its option set. Option ID '17' will be stored in the data, which corresponds to 'Kentucky.'

id text region regioncode division divisioncode statecode
17 Kentucky South 3 East South Central 2 KY
18 Louisiana South 3 West South Central 9 LA
19 Maine Northeast 2 New England 5 ME

Notice, since we use 'system.states' as the option set, we do not need to pull in the options from the 'system.zip_db_dlxbus' sheet. The options: n decorator is applied to prevent this.


Similarly, at QREGION, the system pulls the data from the 'regioncode' column for row '40004', returning the value '3'. QREGION then refers to the system sheet 'system.rgopts' as it's option set. Option ID '3' will be stored in the data, which corresponds to 'South'.

id text
1 Midwest
2 Northeast
3 South
4 West

Assigning a default value

You can assign a default option if no matches are found in the sheet by using the default decorator on the derivefrom tag. This sets a default value and specifies the option ID for the variable. In the example below, default option is '99', and '99. State not found' has been added to QSTATE.

P6. Please enter your ZIP code:
type: text
matchto: system.zip_db_dlxbus
datatype: zip5
maxlen: 5
size: 6

STATE. State
type: radio
derivefrom: QP6 statecode {default: 99}
99. State not found

Applying the 'options' decorator

A variable using derivefrom requires an option set. derivefrom includes all options from the referenced sheet's column by default. To customize this, add options: n to the derivefrom tag to prevent pulling all options. Then, specify options using the optsfrom tag or listing them inline, as shown below.

REGION. One of four geographic regions defined by the U.S. Census
type: radio
derivefrom: QP6 regioncode {options: n}
1. Midwest
2. Northeast
3. South
4. West

Setting up a "footprint" variable

Clients often require that only respondents from a specific ZIP code list are eligible to take the survey or need to track respondents within a particular product "footprint." A typical setup for such scenarios is provided below.

At Q5, the respondent enters their ZIP code. If this ZIP code matches a row in the 'z1934hfstudy.bigbrandname' sheet, the 'foot' column returns a value of '1'. If no match is found, the respondent defaults to '99. Not in BIGBRANDNAME'S footprint'.

id foot
92100 1
92101 1
92102 1
etc. etc. 
5. To ensure we are representing all areas in the US, please enter your 5 digit zip code.
type: text
datatype: zip5
matchto: z1934hfstudy.bigbrandname

BIGBRANDNAME. Footprint from zip code
type: radio
invisible: y
ap: n
derivefrom: Q5 foot {default: 99}
1. In BIGBRANDNAME's footprint
99. Not in BIGBRANDNAME's footprint

Using both a client and internal ZIP code list

In the example below, QS5 asks the respondent for their ZIP code, which is then matched to a client-provided list. Then, the ZIP code is automatically coded to the hidden variable QS5X using the set value widget, which references the company's standardized ZIP code list. All subsequent variables for STATE, REGION, etc. can reference either the hidden variable QS5X or QS5 as needed.

S5. To ensure we are representing all areas in the US, please enter your 5 digit zip code.
type: text
datatype: zip5
size: 6
maxlen: 5
matchto: y2349sk.client_master_file

set value
question: QS5X
value: $QS5

S5X. Zip from S5 for IS FILE variables
translate: n
type: text
datatype: zip5
invisible: y
matchto: system.zip_db_dlxbus

REGION_CLIENT. Client defined regions
type: radio
quotas: y
derivefrom: QS5 regionsclient {default: 97}
97. Region not listed

REGION_IS. One of four geographic regions defined by the U.S. Census
type: radio
quotas: y
derivefrom: QS5X regioncode {options: n}
optsfrom: system.rgopts

Related to

Related articles

Comments

0 comments

Please sign in to leave a comment.