Implement Start and End

mohitveer_sandhu

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))	key	value	start	end
1	cust1	category	c1	March 01, 2022	March 05, 2022
1	cust1	category	c2	March 06, 2022
2	cust2	category	c1	March 01, 2022
1	cust1	key2	value2	date2
1	cust1	key3	value3	date3

 

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, 2022	1	101
March 01, 2022	2	102
March 02, 2022	1	103
March 02, 2022	2	104
. . .	. . .	. . .
March 05, 2022	1	106
March 05, 2022	2	107
March 06, 2022	1	108
March 06, 2022	2	109

 

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:

date	customer_id	category	metric1
March 01, 2022	1	c1	101
March 01, 2022	2	c1	102
March 02, 2022	1	c1	103
March 02, 2022	2	c1	104
. . .	. . .		. . .
March 05, 2022	1	c1	106
March 05, 2022	2	c1	107
March 06, 2022	1	c2	108
March 06, 2022	2	c1	109

 

How can I implement this in Anaplan?

 

 

 

 

mohitveer_sandhu

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