Skip to main content

Varicent ELT Help Center

Rule-Based Assignment

Abstract

Assign values to transactions based on provided hierarchies and rules.

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:

    salestransactionexample.png

  • 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.

    geography_hierarchy2.png

  • 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:

  1. If the geography value is or is a descendant of Canada in the hierarchy, assign the value to Territory 1.

  2. If the geography value is Toronto, assign the value to Territory 2.

The data set for those rules would look something like this:

rule-csv-example.png

Configuration

Use the following configuration options to help create your rules.

Configuring Rule-Based Assignment

  1. In your , add the Data, Hierarchy and Rules data sets to use with the tool.

  2. Click symon_add_icon.png+ Tool.

  3. In the search bar, search for Rule-Based Assignment. Click +Add tool.

  4. Connect the tool to your data sets.

  5. In the configuration pane, enter the following information:

    Table 65. Rule-Based Assignment configuration

    Field

    Description

    Data section fields

    Output Columns

    Specify any columns that you want to output without any data manipulation.

    Date Column

    Specify the desired columns for the output based date-effective hierarchies or rules.

    If you don't have any date-effective hierarchies or rules, you don't need to use this field.

    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 product in ['d'], and a transaction had the product value as computers. The value computer is 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.

    Hierarchy section fields

    Source Column

    Specify the column belonging to the hierarchy. For example, a sales team geography hierarchy, you could choose the Territory column.

    ID Column

    Specify the identifier column for the hierarchy node. For example, a sales team geography hierarchy, you could choose the Territory ID column.

    Parent Column

    Specify the parent column for the hierarchy node. For example, a sales team geography hierarchy, you could choose the Parent Territory ID column.

    By Value

    Select this option when you want 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,)

    Value Column

    Specify which column is specifying the hierarchy nodes value.

    Start Column

    Specify the date where the activities start.

    End Column

    Specify the date where the activities end.

    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).

    Rules section fields

    Output Columns

    Specify the columns that you want to pass through from the rule to be included in the output.

    Definition Columns

    Specify the column that specifies the definition that sets the rule syntax.

    Multiple Values

    If a Split Column Alias is provided, it allows the multiple values and the following:

    • terr1:0.7,terr2:0.2,terr3:0.1

    • terr1:0.7,terr2:0.2,terr3

    Learn more...

    Value Column

    Specify which column is specifying the hierarchy nodes value.

    Precedence Columns

    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.

    Start Column

    Specify the date where the rules start.

    End Columns

    Specify the date where the rules end.

    Output Unmapped

    Select to include any transactions not assigned to a rule in the output. Learn more...



    After you enter the configuration information, you can review the output in the row viewer.

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.1

  • terr1: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.5 would 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-01

  • Number: 'column' 'number' > 0

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

and

Filters data based on the specified conditions. Use to combine multiple conditions.

('segment' in ["asset management"] and 'region' in [...] and 'state' in [...] and 'product' in [...])

or

Filters data based on the specified conditions. Use to combine multiple conditions.

('segment' in ["asset management"] and 'region' in [...] and 'state' in [...] and 'product' in [...]) or ('segment' in ["insurance"] and 'region' in [...] and 'state' in [...] and 'product' in [...])

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.

activitiesExample.png

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:

geoHierarchyExample.png

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:

ruleExample.png

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.