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