Implement Start and End

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?

Accepted answers

All comments

M.Kierepka

Hi @mohitveer_sandhu,

Generally, depending on exact use case, dimensions in the model you use (I don't get it for Config Module, is it transactional list?) and how your lists look like, this is something that might work for you:

Add new line item to "Module 1" formatted as "value" list, dimensioned by date, customer and key (so subsidiary view), with formula like "IF 'Config Module1'.start<= START() AND START() <='Config Module1'.end THEN 'Config Module1'.value ELSE BLANK". Summary should be set to LastNonBlank, and you should have top level on your keys list.

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.

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…