Implement Start and End

Options
mohitveer_sandhu
mohitveer_sandhu Member, ALL USERS, Community Member Posts: 5 Occasional Contributor
in Planning & Modeling

Hi

I have a config module(Config Module1) that contains key-value pairs with start and end dates. For example

customer_id

(format = text)

customer_name (format = list

formula = finditem(list1, customer_id))

keyvaluestartend
1cust1categoryc1March 01, 2022March 05, 2022
1cust1categoryc2March 06, 2022 
2cust2categoryc1March 01, 2022 
1cust1key2value2date2 
1cust1key3value3date3 

 

And I have a module (Module1) with the following schema and sample data:

date

(format = date)

customer_id

(format = text

formula = finditem(list1, customer_id))

metric1 (format = number)
March 01, 20221

101

March 01, 20222102
March 02, 20221103
March 02, 20222104

.

.

.

.

.

.

.

.

.

March 05, 20221106
March 05, 20222107
March 06, 20221108
March 06, 20222109

 

Now I want to add a category column in Module1 and the value of this category column should be based on the value corresponding to key = 'category' and based on customer_id and the date in Module 1 should be between the start and end date of Config Module1. And the result should look like this:

datecustomer_idcategorymetric1
March 01, 20221c1

101

March 01, 20222c1102
March 02, 20221c1103
March 02, 20222c1104

.

.

.

.

.

.

 

.

.

.

March 05, 20221 c1106
March 05, 20222c1107
March 06, 20221 c2108
March 06, 20222c1109

 

How can I implement this in Anaplan?

 

 

 

 

Tagged:

Comments

  • mohitveer_sandhu
    mohitveer_sandhu Member, ALL USERS, Community Member Posts: 5 Occasional Contributor

    @M.Kierepka Module 1 is Transactional Data and is dimensioned on the keys list that contains the unique keys to identify each record separately as Module 1 has multiple records for a single day.

     

    So I cannot change the structure of Module 1 and all I can do is add a new column to this module that will have the category.

     

    Config Module1 is basically a configuration table with the exact same schema as mentioned.

Categories