Derive

The Derive statement is used to generate derived fields based on a field definition created with a Declare statement. You can either specify which data fields to derive fields for, or derive them explicitly or implicitly based on field tags.

Syntax:

Derive [Field[s]] From [Field[s]] field_list Using definition

Derive [Field[s]] From Explicit [Tag[s]] tag_list Using definition

Derive [Field[s]] From Implicit [Tag[s]] Using definition

Arguments:

Example:

In this example we create a calendar definition that can be used to generate date related fields, such as year and month, that can be used in dimensions.

To try out the calendar we need some source data with date fields.

Now we can define a calendar that creates fields for year, month, week, weekday and day number.

The calendar is now defined, and you can apply it to the date fields that have been loaded, in this case OrderDate and ShippingDate, using a Derive clause. There are three alternative ways of doing this.

• Derive fields for specific data fields.

  In this case we specify the OrderDate and ShippingDate fields.

  DERIVE FIELDS FROM FIELDS OrderDate,ShippingDate USING Calendar;

• Derive fields for all fields with a specific tag.

  In this case we derive fields based on Calendar for all fields with a $date tag.

  DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;

• Derive fields for all fields with the field definition tag.

  In this case we derive fields for all data fields with the same tag as the Calendar field definition, which in this case is $date.

  DERIVE FIELDS FROM IMPLICIT TAG USING Calendar;

When you have reloaded the data script, you can find the generated fields, for example OrderDate.Calendar.Weekday under Date & time fields in the Fields asset panel. They are also available in the expression editor and when you create dimensions, and can be used as any other field in visualizations.