Derivative Processor

Derivative Processor
Prev	Chapter 4. Composite DataSource	Next

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”.

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.
Figure 4.22. Add Column Dialog
1. Enter the name of the new column.
2. Select the data type of the derived column from the combo box.
3. 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.

Working with the Derivative processor

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”.
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”.
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.

Figure 4.25. Derived Result

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.
Figure 4.26. Completed Add Column Screen
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.
Figure 4.27. Date Manipulations Result

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.

Prev	Up	Next
Sort Processor	Home	Filter Processor