Tag Archives: SubSt

RIGHT TM1 Function: Use and Syntax

If you are looking for the RIGHT function in TM1, you’ll find that it doesn’t actually exist! Instead it is a combination of SUBST and LONG functions. This post give’s a step-by-step explanation into how to achieve the same result in TM1.

LONG TM1 Function: Syntax and Use

The LONG function in TM1 returns the length of a string as a value.  It is similar to the LEN functions available in both Excel and SQL Server. Syntax of LONG The syntax is LONG(string); where string is either the literal string to be evaluated or a variable that contains the string to be evaluated. Use An example of the use of it is as follows sString = ‘ExploringTM1’; LONG ( sString ); will return … Continue Reading

DatasourceNameForServer and DatasourceNameForClient TM1 Functions: Use and Syntax

Ever wondered how you can vary the data source for a Turbo Integrator process inside the actual process so it becomes dynamic? Well here is a method. Firstly, define a bunch of variables: sCurrentMonth = CellGetS ( ‘System Assumptions’, ‘No Year’, ‘No Month’, ‘Current Month’, ‘String’ ); sFileDir = ‘\\servername\folder\’; sFileName = sCurrentMonth | ‘ ‘ | SUBST(sCurrentYear,3,2) | ‘ Employees.csv’; sFilePath = sFileDir | sFileName; Then use DatasourceNameForServer and DatasourceNameForClient to … Continue Reading

SUBST TM1 Function (like Excel MID or SQL Server SUBSTR): Use and Syntax

The SUBST function is essentially the same as the Excel MID function or the SQL Server SUBSTR function, in that it allows you to select a group of characters of  a specified length from a larger string of characters. Syntax of SUBST The syntax is: SUBST(string, beginning, length); where: string, is the text string that you want to take a slice out of beginning, is the numeric position in string where you want to … Continue Reading

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 … Continue Reading