Custom Operator Example: Making a Lookup Table Using a JSI File

Suppose you have an input source—a database operation, for example—that returns a result set of transaction data. The prices in this data are in various currencies, and you need to express the prices in Euros. You have another input result set that has information about current Euro conversion rates.

The columns in the first input result set are:

ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
NO_OF_SHARES - the number of shares of stock purchased
COST - the cost per share
CURRENCY - the currency in which COST is expressed

The columns in the second input result set are:

CURRENCY - the currency for which the conversion factor applies
MULTIPLIER - the factor by which the original value is multiplied to get the Euro value

Your goal is to produce a result set consisting of:

ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
COST_IN_EURO - the cost in Euros of the transaction

One way to produce the result that you need (and probably the most straightforward way to use WorkSpace Data Federation) is to use a Join operator, employing the “Inner Loop Join” algorithm with the second input result set as the inner table. This produces a result set consisting of:

ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
NO_OF_SHARES - the number of shares of stock purchased
COST - the cost per share
CURRENCY - the currency in which COST is expressed (from the first input)
CURRENCY - the currency for which the conversion factor applies (from the second input)
MULTIPLIER - the factor by which the original value is multiplied to get the Euro value

You can then use a Projection operator to remove the unneeded columns and to compute the COST_IN_EURO column (the formula being NO_OF_SHARES * COST * MULTIPLIER).

Solving the problem with a Custom operator

An alternative implementation for this same functionality is to use a Custom operator to build a lookup table that maps currency to multiplier and then performs the computation as you read in the account result set.

Note: This technique is presented here to illustrate, using a relatively simple problem, how to use Custom operators—not necessarily as a recommendation for best practices.

This example uses a .jsi file as well as a Custom operator. The .jsi file contains the following JavaScript:

var lookupTable = {}; // This will contain the mapping of currency 
//  type to conversion factor.
function setUpLookupTable(rs) {
    if (lookupTable.initialized == true) {
        return; // We only need to read the result set into the map once
    }
    while (rs.next()) { // Iterate over the rows in the result set
        var key = rs.getString(rs, "CURRENCY"); // Get currency type
        var value = rs.getRealValue("MULTIPLIER"); // Get conversion factor
        lookupTable[key] = value; // Save key/value pair
    }
    
    lookupTable.initialized = true; // So we don't try and do this again
}

This code reads the “currency to exchange rate” result set into a hash table. The Custom operator has two inputs—one is called “LookupTableSource,” and the other is called “DataInputSource.”

The logic in the Custom operator itself is as follows (remember that this is a function with a Boolean return value—WorkSpace Data Federation supplies the outer braces, you only type the function body):

// Note that this function requires the lookup table input source 
//  to be named "LookupTableSource" and the main input source to be named
//  "DataInputSource"

setUpLookupTable(LookupTableSourceRS); // Initialize the lookup table

// Check for more rows in DataInputSourceRS. If none remain, return false.
// This also advances the row pointer to the DataInputSourceRS
if (DataInputSourceRS.next() == false) {
    return false;
}

// Get the values of the fields we need from the input result set
var accountNumber = DataInputSourceRS.getInt("ACCOUNT_NO");
var symbol = DataInputSourceRS.getString("SYMBOL");
var currencyType = DataInputSourceRS.getString("CURRENCY");
var numShares = DataInputSourceRS.getInt( "NO_OF_SHARES");

var costPerShare = DataInputSourceRS.getFloat("COST");

// Look up the Euro conversion factor for the original currency
var conversionFactor = lookupTable[currencyType];

// Compute the value of the transaction in Euros
var totalValue = numShares * costPerShare * conversionFactor;

// Store the values in the output result set
outputRS.updateInt("ACCOUNT_NO", accountNumber);
outputRS.updateString("SYMBOL", symbol);
outputRS.updateString("COST_IN_EURO", totalValue);

return true; // Let the runtime know we provided another row