User-Defined Functions

 

Monarch Data Prep Studio allows you to create your own customized functions, called user-defined functions, which you can then use in calculated field, filter, and find expressions. User-defined functions can be created from a combination of "intrinsic" functions, which are the functions built into the application, such as LSplit, and constants, such as text strings, dates and numbers, and operators. You can then add a list of parameters to pass to the function, and even define different forms of the function, with different sets of parameters. You could, for example, create a user-defined function to format a customer number to your specification, return a drive letter from a file path and anything else that you may find useful.

Creating user-defined functions

User-defined functions serve as a quick-and-easy means to apply intrinsic functions customized with constants to various tables in your workspace without having to recreate them repeatedly. For example, if your workspace contains three tables containing a column representing sales net of 12% tax and you wish to calculate gross sales, you would usually need to build the expression "Amount*1.12" three times, once for each of these three tables. You could built the expression once for a single table and then drag-and-drop the change from this table's Change History to the two other tables. However, if the column names don't match (e.g., the columns in the two other tables are named Net_Amount and Total), an error is returned. A user-defined function serves as an excellent solution to this issue. 

  1. Select any column in your table, right-click on your mouse, and then click Column > New Formula Column. The New Formula Column dialog displays.
  2. Click Add User-Defined function  in the Functions column. The Edit Function dialog displays.
  3. Supply a name for your function as well as a description for it. Adding a description will help other users understand what the function is for. You can enable syntax highlighting for parameters by using HTML-style tags. For example, if your function has the form "YearsBetween(startdate,enddate)," you could format your description as "Returns the number of years between <1>startdate</> and <2>enddate</2>."

 

  1. Click Add New Form.
  2. Under Parameters, click Add Parameter. Supply the name and type of the parameters you wish to use for your function. You can also add a test value if you wish. In the example, below, we'lll use the parameter name Number1 and specify its type as Numeric since we are calculating a number. Press Enter on your keyboard to add this parameter to the Parameters list.
  3. Build the function in the Expression box. For example, if you wish to calculate any the gross amount of any number net of 12% tax rounded to two digits, you could create the expression "Round(Abs(Number1)+(0.12*Number1),2)."
  4. Select the data type the returned column must have (Numeric in our example) and then click OK.

 

  1. Click OK in the Edit Function dialog.

 

Your user-defined function is added to the functions list. 

 

Using user-defined functions

  1. Select the column you wish to calculate in your table, right-click on your mouse, and then click Column > New Formula Column. The New Formula Column dialog displays.
  2. Supply a field name for the calculation. This field name will be the name of the new column in your table. 
  3. Build the calculation expression using the available values in the Fields, Functions, and Operators columns.

 

A preview of the calculated column displays at the bottom of the dialog.

  1. If you are satisfied with the results of the calculation, click OK. 

The new column is added to your table.

 

Managing user-defined functions

You can manage uer-defined functions by right-clicking on any column in your table and then selecting Column > New Formula Column > Manage User-Defined Functions .

The User-Defined Functions dialog displays. This dialog contains a list of all user-defined functions available for use in your workspace as well as their form. You can edit, copy, and delete any of these functions by selecting the appropriate button under the Actions column. You can also add a function from this dialog or import functions from a model or workspace file.

 

Importing user-defined functions

  1. Click Import from File in the User-Defined Functions dialog. 
  2. The Open File dialog displays. Use this dialog to locate, select, and open a data prep workspace (.dpwx) or model (.dmod, .xmod) file containing user-defined functions you wish to import.
  3. If you selected a workspace in Step 2, the Import User-Defined Functions dialog displays. Select the name of the user-defined function(s) you wish to import and then click OK.

The user-defined functions are added to the User-Defined Functions list.

 

When a Monarch model containing user-defined functions is imported into a workspace, any user-defined function with the same name as an existing function is disambiguated in the following manner:

  • The name of any function containing the same name as an existing function but different forms or parameters must be disambiguated
  • Any function containing the same forms or parameters as an existing function but a different name will be imported with no change
  • Any function containing the same name as an existing function as well as the same forms or parameters will be ignored

Note that when importing models containing user-defined functions into Monarch Data Prep Studio, only those functions present in the model will be imported. Any functions present because of a link, either strong or weak, will be ignored during the import process.

 

 

© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support