Planual rules for transforming data in Anaplan Data Orchestrator.
8.02-01 Reduce the volume of data passing through a data transform as early as possible
Reducing the volume of data that is processed through a sequence of transforms as soon as possible reduces the processing burden. Use filters and aggregation and only carry across those columns of data that are needed.
relatedToRule
8.02-02 Use the HASH function
The HASH function returns a deterministic unique key that's 56 characters in length based on a parameter value.
You can safely shorten this from 56 characters to n characters (by, for example taking the left n characters) with an insignificant chance of collision based on the expected populated size of the target domain. Shortening the result provides a more readable unique code.
For less than 0.0001% chance of collision, use:
- 10 characters for domains of up to 1,000 items
- 15 characters for domains of up to 1 million items
- 20 characters for domains of up to 1 billion items
8.02-03 Use the TO_DATE function to convert strings to dates
This provides the ability to define the format that the date is in, whereas the general CAST function assumes a date in the U.S. date format: mm/dd/yyyy.
8.02-04 There must be at least one matching column when joining two datasets
This prevents users from mistakenly creating a cartesian product between two large datasets. If you need to do this — for example, to join in a parameter value to be used in a transform — then add a calculated column to both datasets with the same constant value.
8.02-05 Pay attention to filter order
For example, a detail filter is applied to the results of a transform after any column calculations have been applied. So, if you need to filter on the original values, apply the calculation in one transformation view and then base a subsequent transformation view with the detail filter on that.
8.02-06 Only join on equal values when joining two datasets
When joining two datasets, we can only join on equal values since it's not possible to join on the result of an expression or filter on an expression.
For example:
TransactionsDataSet.Productcode = ProductMaster.ProductCode AND TransactionsDataSet.TransactionDate >= ProductMaster.StartDate.
To achieve this, join the datasets for all relevant combinations and apply a filter to the relevant rows.
TransactionsDataSet.Productcode = ProductMaster.ProductCode
Calculate a column for the comparison:
InRelevantDateRange = TransactionsDataSet.TransactionDate >= ProductMaster.StartDate
Add a filter for InRelevantDateRange = TRUE
8.02-07 Convert string values to the appropriate type
Use these functions to convert string values to the appropriate type:
- TO_NUMBER
- TO_DATE
- TO_TIMESTAMP
Use the TO_CHAR function to convert NUMERIC (floats / integers), DATE, and TIMESTAMP type columns into strings in a specified format.
To convert between types, use CAST:

You can use the functions IS_FLOAT, IS_INTEGER, and IS_BOOLEAN to check if a string value is suitable for CASTing into the required data type.
Note: IS_FLOAT and IS_INTEGER don't consider strings that represent numbers with a thousand separators as numbers. Use a formula such as the one below to address the issue:
IF
IS_FLOAT ( SUBSTITUTE ( 'CC Test'.'New column1' , "," , "" ) )
THEN
CAST(SUBSTITUTE ( 'CC Test'.'New column1' , "," , "" ),"FLOAT")
ELSE
0