Scan TM1 Function: How to Use, Syntax and Examples
Scan is a TM1 function that finds 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:
- 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 of SCAN
sSubstring = 'TM1'; sString = 'ExploringTM1'; SCAN (sSubstring, sString);
This will return 10 because the substring ‘TM1’ starts at the 10th position in ‘ExploringTM1’. Scan 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.
Further Example with SCAN, IF and SUBST in a TI Process
# Strip _p from Customer ID IF ( SCAN ( '_p', vCustomer ) = 0); vCustomer = vCustomer; ELSE; vCustomer = SUBST ( vCustomer, 1, SCAN ( '_p', vCustomer )-1 ); ENDIF;
The function is valid in both Rules and Turbo Integrator processes in TM1 and Planning Analytics.