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)
- Example: 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
- Example: The following example is the derivation of a loop variable named
SavedRecordIndex in a Transformer stage:
SavedRecordIndex: GetSavedInputRecord()
- MaskData
- Mask all input characters with the optional mask-byte character, or the '*' character if no
mask-byte is supplied. The mask-byte is an integer mapping to the desired character from the ASCII
table. For example, use a value of 35 to mask input characters with the '#' character.
- Input: string (string), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string
"###############".
MaskData(Link_1.Phone, 35)
- MaskDataKeepFirst
- Mask all input except the first 'keep-first' number of characters unmodified from the beginning
of the string with the optional mask-byte character, or the '*' character if no mask-byte is
supplied. The mask-byte is an integer mapping to the desired character from the ASCII table. For
example, use a value of 35 to mask input characters with the '#' character.
- Input: string (string), keep-first (integer), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string "+1
12##########".
MaskDataKeepFirst(Link_1.Phone, 5, 35)
- MaskDataKeepLast
- Mask all input except the last 'keep-last' number of characters unmodified from the end of the
string with the optional mask-byte character, or the '*' character if no mask-byte is supplied. The
mask-byte is an integer mapping to the desired character from the ASCII table. For example, use a
value of 35 to mask input characters with the '#' character.
- Input: string (string), keep-last (integer), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string
"##########-7890".
MaskDataKeepLast(Link_1.Phone, 5, 35)
- MaskDataFormat
- Mask all input characters with the optional mask-byte character, or the '*' character if no
mask-byte is supplied, while preserving the format. The mask-byte is an integer mapping to the
desired character from the ASCII table. For example, use a value of 35 to mask input characters with
the '#' character.
- Input: string (string), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string "+#
###-###-####".
MaskDataFormat(Link_1.Phone, 35)
- MaskDataFormatKeepFirst
- Mask all alphanumeric input except the first 'keep-first' number of characters unmodified from
the beginning of the string with the optional mask-byte character, or the '*' character if no
mask-byte is supplied, while preserving the format. The mask-byte is an integer mapping to the
desired character from the ASCII table. For example, use a value of 35 to mask input characters with
the '#' character.
- Input: string (string), keep-first (integer), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string "+1
12#-###-####".
MaskDataFormatKeepFirst(Link_1.Phone, 5, 35)
- MaskDataFormatKeepLast
- Mask all input except the last 'keep-last' number of characters unmodified from the end of the
string with the optional mask-byte character, or the '*' character if no mask-byte is supplied,
while preserving the format. The mask-byte is an integer mapping to the desired character from the
ASCII table. For example, use a value of 35 to mask input characters with the '#' character.
- Input: string (string), keep-last (integer), mask-byte (tinyint)
- Output: result (string)
- Example:: If Link_1.Phone contains the string "+1 123-456-7890", the following function
returns the masked string "+#
###-###-7890".
MaskDataFormatKeepLast(Link_1.Phone, 5, 35)
- 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)
- Example: 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)
- Example: 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)
- RegexMatch
- Returns 1 (true) if the pattern is found in the input string, and 0 (false) otherwise.
- Input: string (string), pattern (string)
- Output: result (uint8)
- Example: If Link_1.Regex_Match contains the string “He!!0 w@rld”, and pattern is
”[A-Za-z0-9@! ]+”, then the following function returns 1
(true).
RegexMatch(Link_1.Regex_Match, "[A-Za-z0-9@! ]+")
- RegexReplace
- Searches for the pattern in the input string, and replaces each occurrence with the new
pattern.
- Input: string (string), pattern (string), new pattern (string)
- Output: result (string)
- Example: If Link_1.Regex_Replace contains the string “TEST PATTERN PATTERN TEXT”, pattern
is ”P.T+E”, and new pattern is "REPLACE" then the following function returns "TEST REPLACERN
REPLACERN
TEXT".
RegexReplace(Link_1.Regex_Replace, "P.T+E", "Replace")
- RegexSearch
- Return the 0-based index position of the first pattern match. If not found, return -1.
- Input: string (string), pattern (string)
- Output: result (int32)
- Example: If Link_1.Regex_Search contains the string “TEST PATTERN”, and pattern is
”P.T+E”, then the following function returns
5.
RegexSearch(Link_1.Regex_Search, "P.T+E")
- 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)
- Example: The following example is the derivation of a stage variable named
NumSavedRecords in a Transformer
stage:
NumSavedRecords: SaveInputRecord()
- SetUserStatus
- Sets an internal value for user status.
- Input: user status (string)
- Output: return code (integer)
- Example: The following example sets a user status of "sales job done."
SetUserStatus("sales job done")
Note: If this function is called within the output expression, you must run the Transformer stage in sequential mode.