X
Icon

The 21st Minute


Blog


Blog Image

Using Nested @Functions()

IDEA Tech Tip


One of the most powerful tools in IDEA is the ability to create equations, save them as “.eqx” files, and share them with colleagues. By utilizing the list of @functions in the Equation Editor, you can filter data via Criteria or append new fields to your database. Learning to combine the many different functions in creative ways can open a wealth of new capabilities that you may not have known your IDEA software is capable of.

Syntax

All of the functions used in the IDEA Equation Editor start by typing the “@” symbol followed by the function name and a set of parentheses which contain the parameters that must be entered for the function to work. The parameters are different for every function. Fortunately, the equation editor provides you with a detailed description of each function, including the parameter definitions and examples of use.

To view the description for an @Function, open the Equation Editor and select the one you are interested in. The description will appear in a window on the right.

@Ctod function converts dates stored with a Character field type to a Date field type in IDEA

The first parameter is usually the value being evaluated. In the screenshot above, the @Ctod() function asks for a string. This could be a Character field or could be just written text.

Nesting

You can use an @Function in place of a string or parameter inside another @Function. This is commonly called “nesting.” Nesting is very useful when the string’s or parameter’s value would change from record to record.

When building a nested IDEA equation, it may be helpful to think of it as being very similar to an algebraic equation since they use many of the same rules. For example, the “Order of Operations” rule in algebra dictates that operations within parentheses occur before those outside. This is true when writing IDEA equations as well.

Example:

Let’s say you have three Numeric fields called MONTH, DAY, and YEAR. You’ll need to concatenate those fields into a single Date field if you want to do any sort of aging or other date related analytics. It’s important to note that Numeric fields cannot be concatenated, so the data in the three numeric fields must first be converted to character data, then concatenated. Once concatenated, the resulting value would need to be converted to a date data type. All these steps can be performed with one nested equation similar to this:

@Ctod(@Str(MONTH, 2, 0)+@Str(DAY, 2, 0)+@Str(YEAR, 4, 0), “MMDDYYYY”)

As noted above, the syntax for the @Ctod function is @Ctod(String, Mask). This part of the equation, @Str(MONTH, 2, 0)[email protected](DAY, 2, 0)[email protected](YEAR, 4, 0), serves two purposes. It converts the numeric values in the Month, Day and Year fields to character data type and is used as the String in the @Ctod function. This part of the equation, “MMDDYYYY”, is the Mask. The equation only works because the operations inside the inner most parentheses were completed first.

We hope this will be very helpful to your team. And, as always, if you have questions about this Tech Tip or other IDEA questions, please give our help desk a call at:

888-641-2800 Option 4
Or email us at [email protected]


CaseWare IDEA , Tech Tip



Posted By

By


Related Posts
Wayfair Woes One Year Later
Jul 24 The rise in online shopping has sharply impacted state sales tax revenue. Rather than increasing the tax rate, states are setting parameters to define nexus, wh...
Importing Date Fields
Jul 24 There are times when you import a date field and it just does not work out as expected. Here are ways to address the problem before or after the import.  ...
Tech Tip: Understanding Join and Visual Connector
Jun 19 Using IDEA’s Join and Visual Connector features can help you search for matches and correlations between different data sets, but they are often confused with...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×