The Derivative processor is used to derive one or more new columns through computations on existing fields present in the data source.
The Derivative processor is selected from the menu bar of the Designer Window and then placed on the diagram.
Properties
The editable properties are shown in Figure 4.21, “Derivative Wizard”.
There are three tabs in the Derivative properties: Base, Derived and JavaScript.
The Base tab will display all the fields present in the data source connected to the Derivative processor input.
In the Derived tab there are 4 columns, Column
, Name
,
Type
and Value
which contain input data required for
deriving the new column.
On clicking Add, the dialog box pops up as shown in Figure 4.22, “Add Column Dialog”. The following options are available.
Enter the name of the new column.
Select the data type of the derived column from the combo box.
Enter the formula that will be used to derive the
column or the JavaScript function name in the
Value
text box. Click OK
in the Add Column
dialog box to add the derivative column
to the Derivative Wizard.
If you need more complex JavaScript functionality, enter the JavaScript
code in the Derivative JavaScript
tab. Note that any functions defined within this tab are only
used within the scope of the Derivative - they cannot be accessed
elsewhere. If you need functions to be available throughout all
uses of JavaScript, define such functions in the Composite Diagram
Script
tab instead.
Highlighting row(s) in the Base
tab and right-clicking on them
gives the option to add the selected row(s) to the Derived
tab. Column name(s) containing spacing or symbols will be contained within
square brackets with open and closed inverted commas, [""]
automatically when moved over, but the Value of the column will have to be entered
manually.
To derive two new columns from the Stores data source using the Derivative processor:
Add a JDBC data source, Stores
, to the repository.
Add a Composite DataSource to the repository and open it.
Select Stores.ds
, drag it into the Composite diagram and
drop it.
Add a Derivative processor.
Connect Stores.ds
to the Derivative
processor and connect the output to Result. The
designer window appears as shown in Figure 4.23, “Sample Derivative Flow”.
Deriving a new column using a formula
To calculate the percentage of meat available in the stores:
After connecting the stores data source with the Derivative processor using the Derivative procedure given above, invoke the Derivative Wizard by double clicking the Derivative processor.
The fields available in the data source are
listed in the Base
tab.
Select the Derived
tab. In this window, click
Add Column. The Add Column
dialog box
appears.
Enter the name as meat_percentage
in the text box
provided.
Select the Data type of the column as long
.
Enter the formula
meat/store*100
in the value text box. The dialog box is shown in Figure 4.24, “Completed Add Column Dialog”.
Click OK and the column is added to
the Derived
tab window.
Click Finish and view the Result.
The output is shown in Figure 4.25, “Derived Result”. A new column meat_percentage has been added to the datasource, displaying the percentage of meat space available in the stores.
Deriving new columns using the various Date functions
Date manipulations usually require values such as 5 days ahead, 3 years ahead, 20 days before and 5 months before to be calculated.
Just like the previous illustration, connect Stores.ds
to a Derivative processor.
After connecting, open the Derivative processor by double-clicking the processor or right-click it and select Properties.
The fields available in the data source are
listed in the Base
tab
Select the Derived
tab. In this window, click
Add.
Enter the name of the column as Ahead_5_days
.
Select Date
as the Data Type. Enter
offsetDays(first_opened_date,5);
in the Value text box. This function will calculate 5 days ahead of the day in the given date. Click OK. The "Ahead_5_days" column is added to the Derivative Wizard.
Click Add. The Add Column
dialog box pops up.
Enter name of the column as Ahead_3_years
. Select Date
as the Data Type.
Enter
offsetYears(first_opened_date,3);
in the Value text box. This function will calculate 3 years ahead of the year specified in the given date. Click OK. The "Ahead_3_years" column is added to the Derivative Wizard.
Click Add to add a new column. The Add
Column
dialog box pops up. Enter name of the column
as offset_20days_before
. Select Date
as the Data
Type. Enter
offsetDays(first_opened_date,-20);
in the Value text box. This function will calculate 20 days before the day specified in the given date. Click OK. The "offset_20days_before" column is added to the Derivative Wizard.
Click Add to add a new column. Enter name of the column
as offset_5months_before
. Select Date
as the Data
Type. Enter
offsetMonths(first_opened_date,-5);
in the Value text box. This function will calculate 5 months before the month specified in the given date. Click OK. The "offset_5months_before" column is added to the Derivative Wizard.
After entering the Column the Derivative Wizard appears as shown in Figure 4.26, “Completed Add Column Screen”. Click Finish in the Derivative Wizard.
The output is shown in Figure 4.27, “Date Manipulations Result”. It can be seen that the offset dates are derived and displayed in new columns added to the data source.
Deriving a new column using the Java script function
As an example, to calculate the percentage of Frozen area in the stores:
After connecting the Stores
data source with the
Derivative processor using the Derivative procedure
given above, open the Derivative Wizard
properties.
The fields available in the data source are
listed in the Base
tab.
Select the JavaScript tab window and enter the following code:
function percent() { percentage=frozen/store*100; return percentage; }
Select the Derived tab window. Click Add.
The Add Column
dialog box appears.
Enter the name of the derived field as
frozen_percentage
.
Select long
as the data type.
Enter the function name
percent();
in the value text box. Click OK.
The column is added in the Derived window of the Derivative Wizard. Click Finish and view the Result.
A new column frozen_percentage has been added to the data source, displaying the percentage of Frozen area in the stores.