IBM Planning Analytics Tips & Tricks: The SCAN Function

January 25, 2022

Have you ever needed to parse data by looking for a specific character within your source records? Maybe you want to filter an employee’s last name from a “Lazarow, Lee” or maybe you want to define the year from “12/25/2021”.

All of this can be done with the SCAN function. The SCAN function is used to provide a number that defines the starting location of the first occurrence of a substring. If the substring does not occur in the given string, the function returns 0.

The syntax of the command is:

SCAN (substring, string)

For example:

SCAN (‘b’, ‘abcdef’) returns 2

The scan function can be used to find a specific character or to find a substring that consists of multiple characters.

SCAN (‘def’, ‘abcdef’) returns 4

This can be used in conjunction with the SUBST formula to filter text.

For example:

strTextValue = ‘Lazarow, Lee’;
numCommaLocation = SCAN(‘,’, strTextValue);
strLastName = SUBST (strTextValue, 1, numCommaLocation);

The above code would result in “Lazarow,” since the scan function gives the location of the comma. We can then remove the comma by subtracting 1 from the numCommaLocation value and the result would be “Lazarow”.

And what about a situation where you need to scan twice? Maybe you have a time stamp of “12/25/2021” and you want to pull out the year. We can do this by looking for the first slash, trimming the record, and then looking for the second slash within the remaining text.

strTextValue = ‘12/25/2021’;
numFirstSlashLocation = SCAN (‘/’, strTextValue);
strAfterFirstSlash = SUBST (strTextValue, numFirstSlashLocation + 1, 7);
numSecondSlashLocation = SCAN (‘/’, strAfterFirstSlash);
strAfterSecondSlash = SUBST (strAfterFirstSlash, numSecondSlashLocation + 1, 4);

Put this together and there is nothing you cannot parse!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

Lee Lazarow

View all posts