Declare

The Declare statement is used to create field definitions, where you can define relations between fields or functions. A set of field definitions can be used to automatically generate derived fields, which can be used as dimensions. For example, you can create a calendar definition, and use that to generate related dimensions, such as year, month, week and day, from a date field.

You can use Declare to either set up a new field definition, or to create a field definition based on an already existing definition.

Setting up a new field definition

Syntax:

definition_name:

Declare [Field[s]] Definition [Tagged tag_list ]

[Parameters parameter_list ]

Fields field_list 

Arguments:

Argument Description
definition_name

Name of the field definition, ended with a colon.

Warning: Do not use autoCalendar as name for field definitions, as this name is reserved for auto-generated calendar templates.

Example:  

Calendar:

tag_list

A comma separated list of tags to apply to fields derived from the field definition. Applying tags is optional, but if you do not apply tags that are used to specify sort order, such as $date, $numeric or $text, the derived field will be sorted by load order as default.

Example:  

'$date'Thank you for bringing this to our attention, and apologies for the inconvenience.

Field tags

parameter_list

A comma separated list of parameters. A parameter is defined in the form name=value and is assigned a start value, which can be overridden when a field definition is re-used. Optional.

Example:  

first_month_of_year = 1

field_list

A comma separated list of fields to generate when the field definition is used. A field is defined in the form <expression> As field_name tagged tag. Use $1 to reference the data field from which the derived fields should be generated.

Example:  

Year($1) As Year tagged ('$numeric')

Example:

Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields
Year($1) As Year Tagged ('$numeric'), Month($1) as Month Tagged ('$numeric'), Date($1) as Date Tagged ('$date'), Week($1) as Week Tagged ('$numeric'), Weekday($1) as Weekday Tagged ('$numeric'), DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric') ;
 

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.

Derived fields (full example)

Re-using an existing field definition

Syntax:

<definition name>:

Declare [Field][s] Definition

Using <existing_definition> 

[With <parameter_assignment> ]

Arguments:

Argument Description
definition_name

Name of the field definition, ended with a colon.

Example:  

MyCalendar:

existing_definition

The field definition to re-use when creating the new field definition. The new field definition will function the same way as the definition it is based on, with the exception if you use parameter_assignment to change a value used in the field expressions.

Example:  

Using Calendar

parameter_assignment

A comma separated list of parameter assignments. A parameter assignment is defined in the form name=value and overrides the parameter value that is set in the base field definition. Optional.

Example:  

first_month_of_year = 4

Example:

In this example we re-use the calendar definition that was created in the previous example. In this case we want to use a fiscal year that starts in April. This is achieved by assigning the value 4 to the first_month_of_year parameter, which will affect the DayNumberOfYear field that is defined.

The example assumes that you use the sample data and field definition from the previous example.

MyCalendar: DECLARE FIELD DEFINITION USING Calendar WITH first_month_of_year=4;
 
DERIVE FIELDS FROM FIELDS OrderDate,ShippingDate USING MyCalendar;
 

When you have reloaded the data script, the generated fields are available in the sheet editor, with names OrderDate.MyCalendar.* and ShippingDate.MyCalendar.*.

See also:

 

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?