The new fields area of a derived dataset (defined in detail here https://support.lityxiq.com/608058-Defining-New-Fields-for-a-View) has the option to create multiple new fields at once. This functionality is a powerful way to apply the same basic operation to many variables at the same time. Two use case examples are show below.
Example 1 - Take Absolute Value of Many Fields
Suppose you have many numeric variables, and you want to create a new field related to each of them that contains the absolute value. In this case, you want to maintain the original data, while at the same time creating new absolute value fields. Below is a screenshot of the new field settings to accomplish this.
Some comments on this setup:
- The setting "Define Multiple New Fields" is selected in the first dropdown box.
- 27 variables are selected from the Base Fields list. In general, you would select all fields to which you want to apply this code.
- The Field Name is entered as Abs_[@var]. Each new field that is created will have a name that looks like "Abs_F80", "Abs_F100", etc. The [@var] part of the new field name template is replaced iteratively with each selected Base Field. It is required to use the [@var] template in the Field Name definition so that the result distinguishes uniquely between all of the new fields that will get created.
- The New Field Code also includes the reference [@var]. Again, the process will iterate through each selected Base Field and execute the entered code. The iterated field names (such as F80 or F100 in the example) will be put in place of the [@var] template before the new field code is evaluated. For example, when it processes the base field F80, the code it will evaluate is ABS( [F80] ).
- The result of this operation (when the view is executed) will be 27 new fields Abs_F80, Abs_F100, etc. The original fields F80, F100, etc will still be in the dataset as well (as long as you don't remove them later in the derived dataset).
Example 2 - Update Many Fields to Replace Null with Zero
Another common use case for which multiple new fields can be helpful is the need to replace null values with zero values (or with any value) for many variables. Using the same basic variable list as in Example 1, here is how you would set this up:
There are two differences here compared to Example 1
- The Field Name is entered only as [@var]. Instead of creating a brand new field for each selected Base Field as was done in the first example, it will simply update the selected base fields. This can be seen because the result of iteratively replacing each selected base field name into the simple Field Name [@var] are just the original base field names.
- In the New Field Code, there are two references to the [@var] template. For each Base Field, the code will check to see if it has a null value for a row. If so, the output of the Case/When operation will be 0 for the row. Otherwise, as seen in the ELSE clause, it will be the same value that was originally in the row. More on Case/When statements can be found here: https://support.lityxiq.com/722718-Conditional-and-Null-Handling-Functions.
So, the result of this multiple new field operation will not have any brand new fields created, but will have 27 updated fields.