Utility functions

The utility functions specifies a variety of purposes. Some of the functions include, GetEnvironment, GetSavedInputRecord, and NextSKChain.

The following functions are available in the Utility category (square brackets indicate an argument is optional):

GetEnvironment

Returns the value of the given environment variable.

Input: environment variable (string)
Output: result (string)
Examples. If you queried the value of the environment variable name APT_RDBMS_COMMIT_ROWS then the following derivation might return the value "2048".
```
GetEnvironment("APT_RDBMS_COMMIT_ROWS")
```

GetSavedInputRecord

This function is used to implement the aggregating of data on the input link of a Transformer stage. You call the GetsSavedInputRecord function to a retrieve an input row that you have previously saved to a cache area. The function retrieves the next input row from the cache (in the order in which they were saved to the cache) and makes it the current input row. The retrieved row overrides what was the current input row, and so any derivation using an input column value will use the value of that column in the input row retrieved from the cache, not what was previously the current input row. You must call GetSavedInputRecord in a loop variable derivation, you cannot call it from anywhere else. For example, you cannot call GetSavedInputRecord in the Loop Condition expression. You can call GetSavedInputRecord, multiple times and retrieve the next cached row on each call. Use the SaveInputRecord function to store rows to the cache. GetSavedInputRecord returns the cache index number of the record retrieved from that cache.

Input: -
Output: cache_index_number
Examples. The following example is the derivation of a loop variable named SavedRecordIndex in a Transformer stage:
```
SavedRecordIndex:  GetSavedInputRecord()
```

NextSKChain

This function is used in the Slowly Changing Dimension stage as the derivation for a column with the SKChain purpose code. The function is not used in the Transformer stage. NextSKChain returns the value of the surrogate key column for the next row in the chain, or the value that has been specified to use for the last record in the chain.

Input: last_chain_value (int64)
Output: surrogate_key_value (int64)
Examples. If you specify the following function in the derivation field for a SKChain column in an SCD stage, the output column contains the value of the surrogate key of the next record in the chain, or the value 180858 if this is the last row in the chain.
```
NextSKChain(180858)
```

NextSurrogateKey

Returns the value of the next surrogate key. You must have previously set up your surrogate key source, and defined details on the Surrogate Key tab of the Stage page of the Transformer properties window.

Input: -
Output: surrogate_key_value (int64)
Example. The derivation field of your surrogate key column contains the following function:
```
NextSurrogateKey()
```

PrevSKChain

This function is used in the Slowly Changing Dimension stage as the derivation for a column with the SKChain purpose code. The function is not used in the Transformer stage. PrevSKChain Returns the value of the surrogate key column for the previous record in the chain, or the value that has been specified to use for the first record in the chain.

Input: first_chain_value (int64)
Output: surrogate_key_value (int64)
Examples. If you specify the following function in the derivation field for a SKChain column in an SCD stage, the output column contains the value of the surrogate key of the previous record in the chain, or the value 121060 if this is the last row in the chain.
```
PrevSKChain(121060)
```

SaveInputRecord

This function is used to implement the aggregating of data on the input link of a Transformer stage. You call the SaveInputRecord function to save a copy of the current input row to a cache area. The function returns the count of records in the cache, starting from 1. You can call SaveInputRecord from within the derivation of a stage variable in the Transformer stage. You can call SaveInputRecord multiple times for the same input row. The first call adds the input row to the cache and each subsequent call adds a duplicate of that same input row into the cache. So, for example, if SaveInputRecord is called three times for one input record, then the cache will contain three rows, each identical to the original input row. Use the GetSavedInputRecord function to retrieve the rows that you have stored.

Input: -
Output: cache_record_count (int64)
Examples. The following example is the derivation of a stage variable named NumSavedRecords in a Transformer stage:
```
NumSavedRecords:  SaveInputRecord()
```