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:
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 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
This example will look for _p in the variable vCustomer (Customer ID) and if it exists, strip the _p from the ID. It uses the IF, SCAN and SUBST functions:
# Strip _p from Customer ID
IF ( SCAN ( '_p', vCustomer ) = 0);
vCustomer = vCustomer;
ELSE;
vCustomer = SUBST ( vCustomer, 1, SCAN ( '_p', vCustomer )-1 );
ENDIF;Usage
The function is valid in both Rules and Turbo Integrator processes in TM1 and Planning Analytics.
