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:
- 11 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_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 F160 or F200 in the example) will be put in place of the [@var] template before the new field code is evaluated.
- The result of this operation (when the view is executed) will be 11 new fields Abs_F100, Abs_F200, etc. The original fields F100, F200, etc will still be in the dataset as well.
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]. This difference has nothing to do with the specific use case of replacing nulls. Instead of creating a brand new field for each selected Base Field, 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] is 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 11 updated fields.