TM1 and space characters
- Mar. 21, 2014
Introduction
This article deals with spaces in TM1. How does TM1 handle spaces? This is not an exhaustive article covering everything, rather, a collection of interesting and noteworthy facts. TM1 is space-insensitive, but there is more to be known to not get trapped along the way.
Consider text file data source to a TI process, here represented as Book1.txt in Notepad++. The file contains 2 columns, Tab delimited.
Here is a table containing the exact cell contents:
| Record | Field 1 | Field 2 |
| 1 | test1 | 1 space |
| 2 | test2 and a space | empty cell |
| 3 | a space and test3 | a space and test |
| 4 | a space, test4 and a space | test and a space |
| 5 | a space, test, 4 spaces, a 5 and a space | a space, test and a space |
Adding elements to a dimension
Using a simple DimensionElementInsert statement, we observe that TM1 itself chops off the leading and trailing spaces:
The spaces in the middle will remain though.
Testing the existence of an element
As we all know, a Dimix function can be used to test for the existence of an element in a dimension. Using this simple process code in the Prolog:
DataSourceAsciiQuoteCharacter = '';
m = 1;
While(m <= 5);
vElement = 'test' | NumberToString( m );
AsciiOutput('test_indexes.txt', vElement, 'Index = ' | NumberToString( Dimix ('testwim', vElement) ) );
m = m + 1;
End;
The spaces in the middle will remain though.
I get these results:
… we learn that testing for elements is completely space-insensitive. It does not matter if and where you have extra spaces. Therefore, adding the TRIM function to remove additional spaces, is needless.
Writing data to a cube
Next, how does TM1 write the data to a cube? Would we expect the leading and trailing spaces to survive the CellPutS operation? I created a simple CellPutS statement of the variable coming from the text file data source, straight into a simple cube as below. The Number measure contains a rule to calculate the length of the Text measure.
So, this means that even though the source contains spaces, the spaces are lost when writing to a cube - except spaces that are not leading or trailing. Check out the lengths 5 and 9. Again, to write string data to a cube, you do not need to TRIM.
Populating attributes
As attributes are stored in control cubes by TM1, this follows the previous rule.
Writing to a text file
Doing an AsciiOutput or TextOutput will PRESERVE the spaces! Likewise for output through ODBC.
Empty lines in a Text file data source
If you have a text file with completely empty rows, they will get skipped by TM1! You do not enter the Metadata nor the Data tab for these empty records. Watch out. Though, admittedly, to me this is an advantage rather than a disadvantage. While I know this particular behavior in TI, I never had the need to process completely empty rows.
Other types of spaces
You should also pay attention to characters like Ascii code 160, which is also a type of space, but not quite like the regular space (Ascii code 32). These spaces will not be cut off by TI, and the elements will be deemed different to the elements where character code 32 is used.
Replacing double spaces with single spaces
Now I give you a number of custom routines. To start off, here is a short routine to substitute double spaces for single spaces. V1 is the variable for the first column in the Data source:
##### # Wim Gielis # https://www.wimgielis.com #####V1 = Trim( V1 ); While( Scan(' ', V1) > 0 ); x = Scan(' ', V1); V1 = Insrt( ' ', Delet( V1, x, 2), x); End; AsciiOutput('Single_Spaces.txt', V1 );
Trim only trailing spaces
Here is the code to trim spaces at the end of the string:
##### # Wim Gielis # https://www.wimgielis.com #####v = Long( V1 ); While( ( Code( V1, v ) = 32 ) & ( v > 0 ) ); v = v - 1; End; V1 = Subst( V1, 1, v); AsciiOutput('Single_Spaces.txt', V1 );
Trim only leading spaces
Likewise, here is the code to trim spaces at the start of the string:
##### # Wim Gielis # https://www.wimgielis.com #####v = 1; While( ( Code( V1, v ) = 32 ) & ( v <= Long( V1 ) ) ); v = v + 1; End; V1 = Subst( V1, v, Long( V1 ) ); AsciiOutput('Single_Spaces.txt', V1 );
Text functions
When studying the routines above, you will have noticed that I used the functions LONG, TRIM function, SUBST and others. These functions will use the variable contents INCLUDING spaces. Therefore, Turbo Integrator reads the variable into memory, including the spaces if applicable, but when doing comparisons or writing to a dimension/cube, the extra spaces are neglected.
Comparing strings
If your data source contains only spaces (no real text), the following test will be True:
If( V1 @= ''); EndIf;
The test is using 2 single quotes to represent an empty string. Comparing a string with spaces, to an empty string, will be True. Hence, if you ask the user for a String parameter value, you do not have to TRIM to test whether the user filled in a word, or not. If the user fills in ' ' (he/she typed a space), to TM1 this is the same as '' (empty string).
Indenting element names (non-Excel)
In the past, according to IBM’s technique, we could indent elements in a dimension by inserting prefixes in front of the element name: that could be the alias value, for example. Ascii characters 255 and 160 would be candidates for this according said source. However, using TM1 10.2 I cannot replicate it. Whatever I do, regular spaces, characters 255, 160, … at the front of an alias value: they all get trimmed away. In the middle of a string, these space characters remain. Hence, 't est' written as 't' Alt-0160 'est' is different from 't' regular space 'est'. But then again, good old rules save us…: populating the alias with a rule works to indent the element names: ['Indented alias'] = S: Fill(Char(160), 5) | !MyDim;. In Excel, indenting is much easier to set up.
The end
There is probably more to be said about this, but this article contains the most important things to know and to consider. If you think about other facts too, please drop me a note.
