Ever wanted to create a command similar to an SQL “IS IN” in a TM1 Turbo Integrator process? Or use operators in a TM1 TI process to create a “NOT IN” like command from a SQL? Or wanted to use an OR, an AND or a NOT EQUAL TO command in a TI process?
Well, you can.
If you are looking for the operators to use in a TM1 Rule, please check this post.
TM1 TI Operators
There are some very useful operators available in TM1 TI processes. Use:
- For OR, use a percent sign: %
- For AND, use and ampersand: &
- For NOT EQUAL TO, used tilde: ~
“Is In” Command
Further, to make an “is in” command, just use a series of OR statements between multiple conditions and you will achieve the same outcome. To do the OR, you need to use a % in place of the OR. For example:
IF ( gp_account @= ‘Volume (Units)’ % gp_account @= ‘List Price/CIP %’ % gp_account @= ‘Installation Charge/CIP %’ % gp_account @= ‘Freight Outwards/CIP %’ % gp_account @= ‘ Settlement Discount/CIP %’ % gp_account @= ‘Material Cost/CIP %’ % gp_account @= ‘Factory Cost/CIP %’ % gp_account @= ‘Rework Cost/Std Cost %’ % gp_account @= ‘Install Cost/CIP %’ % gp_account @= ‘Install Rework /CIP %’ % gp_account @= ‘Average CIP/Unit’ % gp_account @= ‘CIP Change %’ );
This will test if any of those values in single inverted commas are in the variable “gp_account” and if so, then the IF will be executed.
“Equal To” Command
You can also use an ampersand (“&”) as the operator to make a command equal to x and y and z.
“Not Equal To” Command
You can also use a tilde (“~”) as the operator to make a command not equal to something. For example using the tilde before the open brackets in the following ~(vCostCentre @= ‘1099’ % CostCentre @= ‘2099’ % CostCentre @= ‘6099’ % CostCentre @= ‘8099’ ) would be the equivalent of saying vCostCentre NOT IN (‘1099’, ‘2099’, ‘6099’, ‘8099’).
Now that is cool!