Add Fields
Adds one or more fields to the input rows. Returns all existing fields plus the newly added field(s).
If an added field has the same name as an existing field, that existing field is overwritten. This allows the modification (including nullification) of existing field(s).
Meta flow is a convenient feature, explained below.
A similar stage is Select Fields. It too has the ability to add fields, but it returns only selected data (fields or calculations). If you simply want to rename existing fields, you may use the Rename Fields stage instead.
Stage as JSON
Create field with constant value
{
"weight": "1"
}
Set 99's to null
{
"q1a": "q1a!=99 ? q1a : null",
"q1b": "q1b!=99 ? q1b : null"
}
Convert missing values to zero (re-base)
{
"q2r1": "q2r1 ?? 0",
"q2r2": "q2r2 ?? 0"
}
Create date fields with label
{
"yyyyq": {
"syntax": "year(date) * 10 + quarter(date)",
"label": "Quarter"
},
"yyyymm": {
"syntax": "year(date) * 100 + month(date)",
"label": "Month"
}
}
Note: See Field Meta for an explanation of label, valueLabels, etc.
Create income rollup with valueLabels
{
"hh_income": {
"syntax": "income < 4 ? income : 5",
"label": "Income Rollup",
"valueLabels": [
{ "value": 1, "label": "Less than $40" },
{ "value": 2, "label": "$40K-$59K" },
{ "value": 3, "label": "$60K-$79K" },
{ "value": 4, "label": "$80K-$99K" },
{ "value": 5, "label": "$100K+" },
]
}
}
Create a gap with valueFormat.
Notice that gap declares nested fields by providing an object as syntax rather than a string.
{
"gap": {
"type": "resultCell",
"syntax": {
"value": "seg2.value - seg1.value",
"valueFormat": "\".2f\"",
}
}
}
Grammar
Conceptual grammar reference, since AddFields can take many forms.
AddFieldsSpec: <AddFieldsObject> or <AddFieldsArray>
AddFieldsObject: {
<field1_name>: <AddFieldsValue>,
<field2_name>: <AddFieldsValue>,
...
}
AddFieldsArray: [
<AddFieldsAssignment>,
<AddFieldsAssignment>,
...
]
AddFieldsAssignment: {
"name": <string>
...<AddFieldsValue>
}
AddFieldsValue: {
"type": <FieldType>,
"label": <string>,
"valueLabels": <ValueLabels>
"syntax": <string> or <AddFieldsSpec>
}
Meta
Meta flows through only in certain limited situations: (??)
Flow-through from Field
{
"awareness": "Q3", // pulls field meta from expression Q3
}
Null Coalescing operator
{
"Q1R": "Q1 ?? 0", // pulls field meta from first term (Q1)
}
If Statement / Ternary Conditional
In either form:
- If statement:
if(test_expr, then_expr, else_expr) - Ternary conditional:
test_expr ? then_expr : else_expr
... meta flows from then_expr, not test_expr or else_expr.
{
// This doesn't retain meta
// It tries to pull meta from constant 3, which doesn't have meta.
// It loses q7 meta since q7 is the else_expr
"q7_rollup": "q7 in (3,4,5) ? 3 : q7",
// This retains meta from q7 since it's the then_expr
"q7_rollup_v2": "q7 not in (3,4,5) ? q7 : 3",
// Note: For a rollup you may want to keep the label, but
// you would probably re-define the valueLabels
}
I don't love this since many times I'm wanting to pull the meta from else_expr. Perhaps if then_expr doesn't return any meta (e.g., it's null or a constant), then we could glean meta from else_expr instead.
Add label and valueLabels from scratch
{
"q7_rollup": {
"syntax": "q7 in (3,4,5) ? 3 : q7",
"label": "My great rollup of Q7",
"valueLabels": [
{ "value": 1, "label": "Item 1" },
{ "value": 2, "label": "Item 2" },
{ "value": 3, "label": "Item 3, 4 or 5" }
]
}
}
Bank of recodes
Just an example
{
// recode: convert missing values to zero in Q1_* series
// meta flows through
"Q1_1r": "Q1_1 ?? 0",
"Q1_2r": "Q1_2 ?? 0",
"Q1_3r": "Q1_3 ?? 0",
"Q1_4r": "Q1_4 ?? 0",
"Q1_5r": "Q1_5 ?? 0",
"Q1_6r": "Q1_6 ?? 0",
"Q1_7r": "Q1_7 ?? 0",
"Q1_8r": "Q1_8 ?? 0",
"Q1_9r": "Q1_9 ?? 0",
}
Use Array format as an alternative
Just remember to provide a name for each array element. Be mindful of the usage of square vs pointy braces to create valid JSON.
[
{ "name": "Q1_1r", "syntax": "Q1_1 ?? 0" },
{ "name": "Q1_2r", "syntax": "Q1_2 ?? 0" },
{ "name": "Q1_3r", "syntax": "Q1_3 ?? 0" },
{ "name": "Q1_4r", "syntax": "Q1_4 ?? 0" },
{ "name": "Q1_5r", "syntax": "Q1_5 ?? 0" },
{ "name": "Q1_6r", "syntax": "Q1_6 ?? 0" },
{ "name": "Q1_7r", "syntax": "Q1_7 ?? 0" },
{ "name": "Q1_8r", "syntax": "Q1_8 ?? 0" },
{ "name": "Q1_9r", "syntax": "Q1_9 ?? 0" },
]
Unrolling a set of pick vars
Javascript can make our lives easier for this task.
Rob TODO: spend some time cleaning up this explanation.
As raw JSON (no helpful Javascript):
[
{ "name": "x_item100", "syntax": "pick1==100 ? x_pick1 : pick2==100 ? x_pick2 : pick3==100 ? x_pick3 : null" },
{ "name": "x_item101", "syntax": "pick1==101 ? x_pick1 : pick2==101 ? x_pick2 : pick3==101 ? x_pick3 : null" },
{ "name": "x_item102", "syntax": "pick1==102 ? x_pick1 : pick2==102 ? x_pick2 : pick3==102 ? x_pick3 : null" },
{ "name": "x_item103", "syntax": "pick1==103 ? x_pick1 : pick2==103 ? x_pick2 : pick3==103 ? x_pick3 : null" },
{ "name": "x_item104", "syntax": "pick1==104 ? x_pick1 : pick2==104 ? x_pick2 : pick3==104 ? x_pick3 : null" },
{ "name": "x_item105", "syntax": "pick1==105 ? x_pick1 : pick2==105 ? x_pick2 : pick3==105 ? x_pick3 : null" },
{ "name": "x_item106", "syntax": "pick1==106 ? x_pick1 : pick2==106 ? x_pick2 : pick3==106 ? x_pick3 : null" },
{ "name": "x_item107", "syntax": "pick1==107 ? x_pick1 : pick2==107 ? x_pick2 : pick3==107 ? x_pick3 : null" },
]
As javascript:
() => {
return [100, 101, 102, 103, 104, 105, 106, 107].map(val => ({
name: `x_item${val}`,
syntax: `pick1==${val} ? x_pick1 : pick2==${val} ? x_pick2 : pick3==${val} ? x_pick3 : null`,
})
}
As javascript with field meta (label, valueLabels):
() => {
const entries = [
{ val: 100, label: "Item 100" },
{ val: 101, label: "Item 101" },
{ val: 102, label: "Item 102" },
{ val: 103, label: "Item 103" },
{ val: 104, label: "Item 104" },
{ val: 105, label: "Item 105" },
{ val: 106, label: "Item 106" },
{ val: 107, label: "Item 107" }
];
return entries.map(entry => {
return {
name: `x_item${entry.val}`,
syntax: `pick1==${entry.val} ? x_pick1 : pick2==${entry.val} ? x_pick2 : pick3==${entry.val} ? x_pick3 : null`,
label: entry.label,
valueLabels: [
{ value: 1, label: "Yes" },
{ value: 2, label: "No" },
{ value: 3, label: "Don't Know" },
]
}
})
}
Idea
Maybe values can be declared as functions, with the prior stage meta passed in parameter.
{
"q1_hack": {
"syntax": "q1 + 5.0",
"label": ({ meta }) => "5 more than " + meta?.q1?.label
}
}