When you select the Computed column type, you must enter a function as the Computed API box cannot be blank. For example:
-
Type func. in the Computed API box, and a drop-down list displays all the functions that are available.
-
Select the parameter Replace, and type the rest of the parameter string syntax. For example func.Replace([TAG_NAME],"PSS","EDW").
-
For more examples of computed API functions, see Functions.
-
Other input columns can be viewed inside each function by typing [ to display a drop-down list.
-
A green check mark indicates that the function is valid.
The following list displays all of the available functions for the Computed column type; each function has a set of parameters that can be viewed.
Function name |
Description |
---|---|
AddDefaultUOMIfMissing |
Applied to a column with a UOM value. If there is no UOM supplied and the specified property maps to a property that is scoped by a UOM type, then the output has the default UOM added to the value. Otherwise, the value is left untouched. Default value is based on SI in the schema. |
Concat |
Concatenates a set of input strings. |
ConvertToHash |
Returns the SHA-1 (Secure Hash Algorithm 1) capitalized hash value for the input string. The SHA-1 algorithm computes a uniquely identifiable fixed hash value for the input string. |
DateTimeColumn |
Input value is converted to date time format and can include time zone, yyyy/MM/dd-HH:mm:ss:fff. |
Decode |
Looks for value matches and replaces them with a new value. |
Divide |
Divides parameters using a numerator. |
GetDefaultUOMIfMissing |
Applied to a column with a UOM value. If there is no UOM supplied and the specified property maps to a property that is scoped by a specific UOM type, then the output is set to that default UOM. Otherwise, the value is left untouched. Default value is based on SI in the schema. |
GetDocumentLatestRevisionsFromTargetSystem |
Returns the latest document revision UID for a master document in the defined configuration scope. |
GetDocumentRevisionsCountFromTargetSystem |
Returns the number of revisions available for a document in the target system. |
GetFileName |
Returns the file name from a file path. |
GetJobDetails |
Returns the value of the property specified in the input string. |
GetMajorRevisionCodeFromTargetSystem |
Uses major revision code from the target system. |
GetMinorRevisionCodeFromTargetSystem |
Uses minor revision code from the target system. |
GetParentObjectClassDef |
Returns the class definition of the parent object. A blank value is returned if the parent object is not found in either the object name column of the input CSV file or in the target system for all the possible class definitions specified in the PossibleParentObjectClassDef parameter. |
GetTargetSystemValueIfEmptyReturnDefault |
Replaces input values with the value from the target system, and if the object specified in the QueryDef parameter is not available in the target system, the default value specified in the default value parameter is returned. |
GetValueFromServerManagerSettings |
Checks for the property name in the Settings node for a site in the SDx Server Manager and returns the property value. |
GetValueFromTargetSystem |
Replaces values with the value from the target system. |
GetVersionNumberForRevision |
Returns the latest version number of the document revision from the target system. If the Allow Version Creation flag is turned on in the job definition, it will increment the version number by 1 as an output. |
IndexOf |
Creates an index of parameters; part 1 is input string and part 2 is search string. |
InvertedFilter |
Checks for the property name in the inverted CSV file that matches the property name configured in the computed column. The corresponding computed column is then evaluated and the property value is imported into staging system. |
Join |
Joins parameters with same separator. |
Left |
Input is from the left by the specified length in the computed column. |
Length |
Value in length as an input string. |
Minus |
Reduces initial value by another value being subtracted. |
Multiply |
Multiplies input value by a value. |
PadLeft |
Takes the input string and adds padding to the left using a padding character to match the string length. |
PadRight |
Takes the input string and adds padding to the right using a padding character to match the string length. |
RegexMatch |
If matches a regular expression (such as uppercase characters) returns True, if not then returns False. |
RegexReplace |
Replaces existing regular expression characters (such as uppercase characters) in the name with new characters. |
Replace |
Replaces existing characters in the name with new characters. |
Right |
Starts the count from the right by the specified length in the computed column. |
Split |
Splits the input string using a given character from the string. |
SplitAlphaNumericSequence |
Splits up an alpha numeric sequence into separate values. |
SubString |
Starts input string from sub string. |
Sum |
Adds values together so changes input values to a new value. |
ToLower |
Changes the input string value to lower case. |
ToUpper |
Changes the input string value to upper case. |
Translate |
Replaces a sequence of characters found in a string with another set of characters, a single character at a time. |
Trim |
Trims a character value. |
TrimEnd |
Trims a character value from the end. |
TrimStart |
Trims a character value from the start. |
YMDColumn |
Input value is converted to the yyyy/MM/dd format. |
-
Microsoft .NET methods calculate some of the computed columns. For more information on how some computer columns function, see Computed column .NET methods.
-
Comma decimal separated values in your CSV file must be surrounded by double quotes, for example, "123".
-
When a CSV file uses comma decimal separators, you have to create a computed column to convert the comma separated value to a decimal value. Create a computed column, map it to the property that was mapped on your physical column, and run this computed column function Func.Replace([PHYSICAL COLUMN], ",", ".").
-
You can also make use of HxGN SDx environment- and session-related variables, for example, Job Name, UserName, and Current query configuration. These are accessible using the @ character. For further information, see Environment variables for a list of all the variables supported.
-
When you use the Replace function to replace the @ symbol, Data Validator considers the @ symbol as an environment variable and does not replace it. To replace the @ symbol, you must create a column of Constant type, and give it the value of @. For example, Func.Replace([PHYSICAL COLUMN], "[CONSTANT COLUMN]", "1"). Here, this example replaces the @ symbol with 1.
-
When you use the GetParentObjectClassDef function, if the second parameter, ObjectClassDef, is not available in the CSV file, you can use another computed column to get the class definition of the object.
For example, if the CSV file for SPO/E system tags provides an object with the classfication Cooling Tower, we can get its class definition of 'SPXTagEquipment by using the computed column function: func.GetValueFromTargetSystem("true", "#SPXTagClass,.Name=[TagClass]","-SPFClassMember+TagClassClassDef.Name")).
-
The GetValueFromServerManagerSettings computed column does not support the following properties in the Settings node for a site in the SDx Server Manager:
Property name
Property name
Data Source
Multiple active result sets
User ID
Site path
Password
Site virtual directory
Initial Catalog
InternalServicesSSLEnabled
RequireHttps
-
-
The two computed columns, GetValueFromTargetSystem and GetTargetSystemValueIfEmptyReturnDefault, support multiple relationship expansions, but do not support edge definitions in the QueryDef parameter.
-
The three computed columns, GetJobDetails, Split, and SubString, return blank outputs if the input values are invalid.
-
Example 1: GetJobDetails – func.GetJobDetails([PROPERTY_NAME]) - If an invalid property name is provided as input, such as, VTLJobWorkflow. The correct value is VTLJobWorkflowName.
-
Example 2: Split - func.Split("TAG_NAME","_","2") – If the first parameter is the string itself, and the "_" character is used for splitting, the third parameter can be 0 or 1, but not 2.
-
Example 3: SubString - func.SubString("TAG_NAME","0","9") – The third parameter can be any integer from 0-7 as the string, TAG_NAME, consists of seven characters, not 9.
-