Defining New Fields for a Derived Dataset

After opening the settings dialog for the derived dataset (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View), follow these steps to define any new fields to be created.  It is not required that a derived dataset have any new fields defined.

1) If the New Fields panel is not already opened, click on that panel header to open it.  If new fields have already been defined for this view, as in the example below, you will see a list of all fields currently defined.  Note that the order that these appear in the list is intelligently figured out by LityxIQ, so that variables that build upon each other are shown in order top to bottom.  Fields with code errors will be shown in red, with an explanation of the error provided as well.

 

Each new field listed will have three options available:

  • Edit - clicking this link will open the dialog to edit the new field.  This is explained in more detail below.
  • Copy - click this link to make a copy of this new field definition.
  • Delete - click this link to delete the new field completely (a confirmation dialog will appear first).

To create a completely new field, click the Create New Field button.  The concept for creating New Aggregation Fields is described here: https://support.lityxiq.com/777879-Create-a-New-Aggregation-Field.



2) After clicking the Create New Field button, or 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.

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.

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.

 

3) When finished, click Save, or click Cancel to cancel your changes.