Rule-Based Assignment
The Rule-Based Assignment tool analyzes the data sets based on the transactions and the rules in place for each transaction. Then it applies the rules and maps a value to the data set according to the rules. Gather your transactional data, define your geographical hierarchy and rules, and then run the tool. This tool sorts your transactional data into your hierarchy based on the hierarchy definitions and rules.
For example, you have sales data with transactional activities. You can define the rules for the sales transaction, such as what sales person is associated to each transaction or what territory the transaction is associated with.
You need three different types of data sets for the Rule-Based Assignment tool:
Data: Use data sets with transactional data, such as sales transactions.
For example, a sales transaction structure could include the following information:
Hierarchy: Use a hierarchy data set with a chosen structure to organize your data, such as by employee structures, product categories, customer segments, or geographical regions.
For example, the organizational chart represents a sales team with a Sales Director John Doe, at the top level. Reporting to the Sales Director are two Sales Managers, Jane Smith and Sarah Wilson. Each Sales Manager oversees a group of Sales Representatives. The Manager ID column indicates the ID of the employee's direct manager.
Rules: Use a data set with rules defined for your data, such as rules for a sales transaction to be assigned to a certain territory. These rules can be based on conditions, comparisons, or any other logic that aligns with your business requirements. For more information, see Create rules.
For example, the rules could be If the salesperson resides in the Eastern region, assign the transaction to the East territory. If the salesperson resides in the Western region, assign the transaction to the West territory. If the salesperson resides in the Central region, assign the transaction to the Central territory.
Create rules
Create text-based rules in the editor of your choice, such as Excel or a text editor, and then import them into Varicent ELT. Then, use these rules to apply and assign values to transactions.
In your rule data set, create a definition column, with the following syntax: Column(value). The (value) is the actual value or the ID of the value. For example, the value could be a territory, such as TER01, TER02, and so on. If you create a rule to assign geographies to different territories, you would use the following rule: 'geo' in ['1'].
For example, you have a rules data set with two rules to assign territories to:
If the geography value is or is a descendant of Canada in the hierarchy, assign the value to
Territory 1.If the geography value is Toronto, assign the value to
Territory 2.
The data set for those rules would look something like this:
Configuration
Use the following configuration options to help create your rules.
Go to the Pipes module from the side navigation bar.
From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.
From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.
In your pipe, add the Data, Hierarchy and Rules data sets to use with the tool.
Click
+ Tool.The Tools modal opens, where you can add tools, such as the Aggregate tool, to your pipe.
In the search bar, search for Rule-Based Assignment. Click +Add tool.
Tip
You can also find the Rule-Based Assignment tool in the Sales Performance section.
Click the tool node and drag the line to the next tool to connect the tools. If you need to undo the action, click the line and then click Unlink.
In the configuration pane, in the Data section, under Output Columns, specify any columns that you want to output without any data manipulation.
Under Date Column, specify the desired columns for the output based on date-effective hierarchies or rules. If you don't have any date-effective hierarchies or rules, you don't need to use this field.
Under Match Leaves Only, select to enable if you want to apply hierarchy values, when rules are matching based on the hierarchy ids versus text.
For example, using the product hierarchy, if the rule is
productin['d'], and a transaction had the product value ascomputers. The valuecomputeris not a "leaf" hierarchy node, because it has a laptop and desktop as it's children in the hierarchy. This value wouldn't be considered to match the rule as it doesn't match the transaction.In the Hierarchy section, under Source Columns, specify the column belonging to the hierarchy. For example, a sales team geography hierarchy, you could choose the
Territorycolumn.Under ID Column, specify the identifier column for the hierarchy node. For example, a sales team geography hierarchy, you could choose the
Territory IDcolumn.Under Parent Column, specify the parent column for the hierarchy node. For example, a sales team geography hierarchy, you could choose the
Parent Territory IDcolumn.Select By Value to have the parent to be referred to by it's parent column value instead of by the parent ID.
This configuration is related to the hierarchy parent column, id column and value column configurations. Depending how you generate your hierarchy files, the parent column could be referring to the hierarchy parent node by id or by value.
For example, you would use the following hierarchy and select By Value:
(id, value, parent):(a, terr01, Canada)(b, terr02, Toronto)(c, terr03, North York)(d, all,)
Under Value Column, specify which column is specifying the hierarchy nodes value.
Under Start Column, specify the date where the activities start.
Under End Column, specify the date where the activities end.
Under Attribute Columns, specify any attributes for the hierarchy. Different product hierarchies can have only product codes, and the hierarchy has attribute columns segments.
For example, the hierarchy is
id, value, parent, segment, with this it would look like this hierarchy:(0001, a, x, asset management),(0002, b ,y, insurance).In the Rules section, under Output Columns, specify the columns that you want to pass through from the rule to be included in the output.
Under Definition Columns, specify the column that specifies the definition that sets the rule syntax.
Click to add Multiple Values. If a
Split Column Aliasis provided, it allows multiple values and the following:terr1:0.7,terr2:0.2,terr3:0.1terr1:0.7,terr2:0.2,terr3
Under Value Column, specify which column specifies the hierarchy nodes value.
Click to add Precedence Columns to specify which set of rules are used first. For example, if a rule is present in two rule sets and was matched in the first rule, it won't be matched in the second rule. Sort the precedence by ascending or descending columns.
Under Start Column, specify the date when the rules start.
Under End Column, specify the date when the rules end.
Select Output Unmapped to include any transactions not assigned to a rule in the output. Learn more...
Multiple Values
In the Rules section, if you select to enable the Multiple Values option, the tool parses the provided rule value column for multiple rule values. For example: TER01, TER02, TER03.
If you have a split column alias, it allows multiple values and split values. For example:
terr1:0.7,terr2:0.2,terr3:0.1terr1:0.7, terr2:0.2,terr3
Multiple Values also has the following behavior:
If a data record matches the rule, the tool outputs a record for each of the values.
If you provide a split column alias, the tool adds a column with the provided alias. The values would be the specified split value per rule value.
For example,
"TER01":0.25,"TER02":0.25, and"TER03":0.5would output a record with each of the rule values (TER01,TER02,TER03) with their respective split values (0.25,0.25,0.5).
Hierarchy Attributes
In the Hierarchy section, there are hierarchy attributes to configure to indicate which columns in the hierarchy input data are attributes.
You can create rules specified to match attributes, such as:
Text:
'column' 'text' in ["a", "b", "c"]Date:
'column' 'date' > 2022-01-01 and 'column' 'date' < 2023-01-01Number:
'column' 'number' > 0
You can create rules specified to match transaction data attributes, such as:
Rules with operations on
Text,Numeric,DateandDatetimetype columns:Text: Supported operations:
==and!=,inandnot inFor example,
(’size' in [“medium”])Numeric: Supported operations:
>,<,>=,<=and!=,in,not inandbetween.For example,
('amount' > 100),('amount' in [100]), or('amount' between 100 and 200)Date: Supported operations:
>,<,>=,<=,==, and!=,in,not inandbetween.For example,
('date2' == 1992-01-01T00:00:00Z), or('date2' between 1992-01-01T00:00:00Z and 1992-01-02T00:00:00Z)
Rules with
Betweenoperation forNumericandDatetimetype columns. See Rules with operations on Text, Numeric, Date and Datetime type columns for examples.Rules with comparison between two columns of the same data type:
Supported operations:
>,<,<=,<=,==and!=For example, if you compare two amount column of number types:
('amount' >= ‘amount2').
Single quotes
Columns and attribute columns are always enclosed by a single quote, such as 'product'. For example, 'product' in [...] and 'product'.'size' in [...].
For values, use single quotes to enclose hierarchy ids. For example, assuming you have the following hierarchy, (id, value, parent): (a, laptop, c), (b, desktop,c), (c, computers, d), and (d, all,) the following would match:
'product'in['a']matches transactions for all laptops.'product'in['c']matches transactions for laptops and desktops.
Double quotes
Use double quotes to enclose text. For example, if you have 'product' in ["calculator"] would match all transactions for calculators.
Output Unmapped
If you select to enable the Output Unmapped option, any data records not mapped by any rule is included in the output with empty values for the rule columns.
Operators
Varicent ELT offers the following types of calculation operators to use in your formulas:
Operator | Description | Example |
|---|---|---|
| Filters data based on the specified conditions. Use to combine multiple conditions. |
|
| Filters data based on the specified conditions. Use to combine multiple conditions. |
|
Usage example
Sort your transactional data into your hierarchy based on the hierarchy definitions and rules. In this example, you want to assign all sales in your transactional data to the proper geography so the sales person can get the proper commission for the sale.
In your activity data set, you have the columns geography, product and date sold, this would read as someone in Toronto, Ontario purchased a Macbook on February 1, 2022. The following is an example of a transactional activity data set with the product type, date sold and geography the product was sold in.
In the hierarchy data set, you have the territories and where each territory rolls up in to. For example, Toronto rolls up into Ontario, which rolls up to Canada. The following is an example of a hierarchy containing geographical hierarchy rules:
In the rules data set, you have the values and definition for each defined territory. The following is an example of the rules data set:
Based on the transactional activity, hierarchy and rules, the sale of the Macbook in Toronto would be put into geo [1], which is TER01. The credit for the sale would go to that territory grouping.