Derived Dataset New Fields Editing Window


After clicking the Create New Field button, or when editing an existing field, the Define New Field dialog is shown.  The options are explained below.

Single/Multi - Select whether you are defining a single new field, or multiple fields at once.  If you select Multi, you will also see the Base Fields option made available.  See the document https://support.lityxiq.com/502396-Create-Multiple-New-Fields-at-Once for more help on creating multiple new fields.

Field Name - Enter the name for the new field, or the name of an existing field to update it. If you are creating/updating multiple fields (the Multi setting above), you must use the template [@var] somewhere in the field name, which will be replaced with each selected base field in turn.

Base Fields - This option becomes available when creating multiple new fields at once.  Select the base variables that will be iterated through to create multiple new fields.

String Length - This optional setting can be used to direct LityxIQ to ensure the final output data type of the new field is a string with the given length.  By default, this is set to 0, which means that LityxIQ will determine the appropriate field type and format based on the new field code entered.  In some cases, such as using the SPLIT_PART function and CASE/WHEN statements, it is recommended to set this string length option appropriately to help LityxIQ determine the correct value.

New Field Code - Enter the code for defining the new field.  A wide variety of functions ranging from simple to complex are available.  For example, this may be a mathematical formula or string manipulation formula.  Help for the available functions can be found in various documents such as https://support.lityxiq.com/090447-String-Functions or https://support.lityxiq.com/036770-Mathematical-Functions among others.  Help is also available directly in the interface by clicking the Code Help button.  Some helpful hints:

  • When referring to existing variables in the formula, be sure to enclose their names in square brackets.  For example: [UserID]
  • You can reference configuration variables in the formula using the standards for referencing a configuration variable as described more here: https://support.lityxiq.com/084396-Create-and-Utilize-a-Configuration-Variable.  For example: {@LastMailDate}.
  • When defining multiple new fields, you can reference the base field name using the special code [@var].
  • The validity of the code will be checked after clicking Save.  Fields with errors will be shown in red in the new field list.
  • You can use the Wrap Code button to have the code word wrap itself onto new lines for coding lines that are very long.  You can also use the Enter key to create space and new lines within your code.
  • Note that the entire new fields window can be enlarged by dragging out at the corners.  This can help provide more room on the screen to see your code.
  • You can include a comment on each line of code for the new field.  To do this, at the end of a line, preceded your comment with two hyphens "--" .  Anything entered after the hyphens, until the end of that line, is not interpreted as code.  Note that you must use the Enter key to continue your new field code definition on a new line after entering a comment in this way.
    • Example:

CASE WHEN [Age] <= 25 THEN 0 -- Under 25

WHEN [Age] <= 55 THEN 1 -- Middle age

ELSE 2 -- Seniors

END

 

Available Fields - This drop down shows a list of all fields currently in the dataset (including any other new fields that have been defined).  Use this tool to easily add existing field names into your formula.  First, select the variable name, and then click ‘Insert This Field’.  The variable will be inserted into the formula with the required square brackets already surrounding it.  Configuration Variables also appear in this list, and will be inserted with curly braces.

Clear - Click the Clear button to clear out the currently entered formula.

Code Help - the Code Help button opens a mini-window that you can use to search and browse basic documentation for available functions.  You can also use its Insert Code button to automatically insert a code template for the selected function.

 

Click Save when finished, or click Cancel to exit without saving your changes.