Mapping Data Based on Combination of Codes/Number

Hi All,

I would like to hear your idea or case regarding the topic.

What needs to be build is to map the code based on combination of codes/number (please refer to the attached image that describe mapping logic table).

I can create such logic but too much complicated to maintain(Use tons of IF, LEFT, and such).

The table is changed every quarter so that I would like to create the logic in Anaplan as simple as possible.

Do you have any idea or cases that you can share with me regarding that?

If you have any questions, please let me know.

Thanks!

Community_QA_55063.png

Accepted answers

DavidSmith

@MaoNinomiya

Apologies for the delay, but I think I have a solution. It is complex and a little messy, so I have included it in the attached.

It seems to have satisfied your example data, but obviously there are a lot of patterns missing.

However, hopefully it shows you some principles of how to break down the mapping formula into manageable and testable pieces!!

I hope it helps

David

Mapping_problem_56102.docx

All comments

DavidSmith

In a word - Messy!

To avoid a series of IFs, I would, instead, create a series of boolean modules dimensioned by Code to be mapped and the different rule logic for the different codes/number patterns.

Some do not apply, as per your example, Code C, D ,E and F are not applicable for Mapped Codes A-F, so you would set those to TRUE.

You will probably need line items for each pattern, but splitting it up this way is the most efficient, but you will need to update the formulas/add more

Can you post some example data to help understand the formats of Code A - Code F patterns?

It's always easier to work with data than the theory

David

MaoNinomiya

Hi David,

Thanks for your quick response. And yap, it's super complicated.

Here is some examples for each code pattern. (M5 pattern is more complicated so I avoid posting that.)

Code A	M6	2L2111	1820	A2E	M15	JAC110A	A9B
Code B	M1	2L2111	1820	A1D	A19	JAC440E	C8D
Code C	MD	2MD601	1820	A2E	M20	JAC220E	B6O
Code D	MD	2MDJ01	1820	A2E	A12	JAC120D	E9F
Code E	M6	2MD601	1850	A1R	QRF	JAC220E	B6O

For example,

Code A: M6(=M6), 2L211(<>2MD5*), 1820(<1829), rest of data can be anything because logic table equals "*".

Thanks!

Mao

DavidSmith

@MaoNinomiya

Apologies for the delay, but I think I have a solution. It is complex and a little messy, so I have included it in the attached.

It seems to have satisfied your example data, but obviously there are a lot of patterns missing.

However, hopefully it shows you some principles of how to break down the mapping formula into manageable and testable pieces!!

I hope it helps

David

Mapping_problem_56102.docx

JaredDolich

Wow, @DavidSmith

Impressive solution. I knew it could be done in Anaplan.

I wish I had read this a while ago.

I think this challenge might be a common one and worth making a best practice out of what you've explained in your word document:

I had to do something similar for a client that needed to convert GL transactions into a common exchange rate based on the cost center, region, and transaction type.

They had a lot of wildcards; for example, if the cost center started with "110" then use a certain value.

I created a system module for each dimension and one that only contained the exceptions (combinations of the three dimensions) instead of creating one for every possible combination. If the combination returned was blank then I knew i didn't need to go any further.

Also a system module that mapped the needed exchange rate.

From there I used the Booleans.

Since most transactions needed no conversion the Booleans filter them out first.

@MaoNinomiya situation is far more complicated than mine but the concepts appear to be the same.

Thanks again for taking the time to build this out. I kept a copy - I'm sure I'll run into this situation again.

DavidSmith

@JaredDolich

There is always the "why" question

Why do we need to that complexity in the mappings?. when you resolve through the patterns you often can rationalise and simplify

David

Misbah

@DavidSmith

Wish you had concluded by saying Pattern Logic - Magic Code instead of Pattern Logic - Match Code:)

Copy downloaded and saved

Thanks,

Misbah

DavidSmith

@Misbah

Especially for you!!!

Misbah

@DavidSmith

Haha! Thank you so much

usman.zia

Great solution @DavidSmith thanks for shaining!

This is complex but it a great solution for avoiding large if statements!

MaoNinomiya

Thanks, David!

Your solution is almost the same with my proposal to the client (So I was relieved to hear your solution).

Quick Links

Can you help?

Unanswered questions

OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385