Scan TM1 Function: Syntax and Use

Scan is a TM1 function that find the numeric position of the first instance of a specified substring within a string. If the substring is not found, it returns zero. It is essentially the same as the FIND command in Excel and CHARINDEX in SQL Server.

Syntax of SCAN

The syntax is:

SCAN(substring, string); where

  • substring is the substring you want to find
  • string is the string you are evaluating

both substring and string can be string based variables.

Example

sSubstring = 'TM1';

sString = 'ExploringTM1';

SCAN (sSubstring, sString);

This will return 10 because the substring ‘TM1’ starts at the 10th position in ‘ExploringTM1’. It is really useful when combined with a subst command to find the position of a string and then extract either up to that position or from that position onwards.

The function is valid in both Rules and Turbo Integrator processes in TM1 and Cognos Express.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This entry was posted in Rule Functions, TI Functions and tagged Scan, SubSt. Bookmark the permalink.