In some TM1 systems, advanced data transformations are needed to convert and homogenize information between TM1 and the application, especially for dates and Boolean values (T/F, True/False, Y/N, Yes/No, etc). Rather than relying on the application to do the work, TM1Connect provides conversion capability using Advanced Mappings. Clicking on the Advanced Properties button on the column in the Mappings screen will display the following dialog:


Advanced Mappings


With the advanced mappings you can adjust the formula used in generating the column values as well as apply advanced conversions and transformations on the data. The following properties are available in the advanced mappings:


hmtoggle_arrow1        Column Name

The Column Name field is a string field which will allow you to change the name of the column exposed to the application. This name cannot contain any special characters, spaces or numbers as the first character.

hmtoggle_arrow1        Formula

The Formula field will be defaulted with the formula for the column the view was created with but the formula can be changed to produce a different results. Formulas in TM1Connect are use a token-replacement syntax for determining the results. They are also sensitive to the Data Type setting on the Mappings page.


For String Data Type

Token replacement means that all text outside a specified token will be treated as prepending or appending text without having to supply functions, quotes, string concatenation syntax.


For example, with a formula of:


Dept ${.RowDimensions[1].ID} - ${.RowDimensions[1].Name}


Will result in ID and Name fields (of the row dimension) being concatenated together and prefixed with 'Dept'. For example:


Dept 1100 - Human Resources


For Numeric Data Type

When a formula is evaluated for numeric data types, operators such as +, -, / and * can be used to apply basic math to the value.


For example, with a formula of:


${.Columns[1].Value} * 0.75 + .1


Will result in multiplying the value in column 1 by .75 and then adding .1 to that result.




hmtoggle_arrow1        Enable Conversion on Output

Conversions on Output should be used when a specific format, such as a date or a Boolean value (T/F), is required by the application. Selecting this check box will display additional options:


Conversion Settings


Similar to the Formula, conversions are sensitive to the column data type specified in the Mapping Properties screen.


For String Data Type

If the column is specified as a string value in the Mapping Properties screen, the results will be returned from TM1Connect as a string value even if the value in TM1 is numeric, or a string measure with a date value in it, or a numeric value that represents a date etc.


For example, assume a measure called Date contains a numeric date value in TM1 (stored in integer format), but the application requires a date to be a string data type that uses the format yyyy/MM/dd. TM1Connect understands the transformations required (TM1 date vs UTC) and will convert the TM1 numeric value to a date using the yyyy/MM/dd format by specifying the options shown below:


Convert to Date


For Numeric Data Type

When the output data type is numeric, TM1Connect will attempt to perform a numeric conversion if possible, even if the TM1 measure is a string. If the conversion fails, a zero will be returned.


For Date Data Type

When the output data type is date, TM1Connect will attempt to perform a numeric conversion whether the TM1 measure is a string or a numeric. If it is a string, TM1Connect understands a variety of date formats that can be received from the view (such as YYYY/MM/DD or MM/DD/YY or MMMM DD, YYYY) and convert them to a UTC encoded date.


For Boolean Data Type

When the output data type is Boolean, TM1Connect will attempt to convert to a Boolean value true/false using the following conversion:


Y, Yes, YES, 1, T, True, TRUE, will be converted to a Boolean value true

N, No, NO, 0, F, False, FALSE will be converted to a Boolean value false




NOTE: Data types other than String or Numeric are not supported in the exports for QlikView.