Use the Computed column - HxGN SDx - Update 63 - Administration & Configuration

Administration and Configuration of HxGN SDx

Language
English
Product
HxGN SDx
Search by Category
Administration & Configuration
SmartPlant Foundation / SDx Version
10

When you select the Computed column type, you must enter a function as the Computed API box cannot be blank. For example:

  1. Type func. in the Computed API box, and a drop-down list displays all the functions that are available.

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