Issue: Combine 2 columns

RichardG

Hello all,

 

I am fairly new to Anaplan and I would need help with one task. I have 2 columns and I would need to create additional 3rd column, where the first 2 are combined. The combination should work as follows: look for a string in one column, if you find the string, assign the string, if its blank assign the value from the other column. Both column A and Column B refer to the same names, but the information in Column A is more accurate, so I'd like to use that one if its available.

 

Column A	Column B	Result
Adam K.	Adam	Adam K.
Daniel S.	Daniel	Daniel S.
Peter M.	Peter	Peter M.
	Martin	Martin
	Anna	Anna
	Emily	Emily

 

How can this be achieved in Anaplan please?

 

Thanks a lot,

Richard

rob_marshall

@RichardG 

 

It would be better to not use Text but rather use list formatted line items, but regardless, the formula could be:

 

if isnotblank(Column A) then Column A else Column B

 

 

Rob

RichardG

Hello @rob_marshall ,

 

thank you for the solution. In both columns I actually have list members and when trying out your formula error message displays:

Any suggestion how to approach this?

 

Thank you!

 

Best,

Richard

 

rob_marshall

@RichardG 

 

Are Columns A and B line items or list members?  Can you take a picture of your blueprint?

RichardG

Hi @rob_marshall ,

 

both column A and B are line items, but the format is list.

 

The column A in the example is the line item "final mapping" and the column B would be "Center" which is a list member.

 

Best,

Richard

rob_marshall

@RichardG 

 

Ok, the reason the formula didn't work is because Column A and Column B are different lists.  So, you will have to use Text for the final result:

 

if isnotblank(Column A) then name(Column A) else Name(Column

 

or if you use codes in your lists (which you should), it would be:

 

if isnotblank(Column A) then code(Column A) else code(Column

RichardG

@rob_marshall ,

 

Is it possible to match 2 string values solely on the strings in the field? What you suggested works, however since both are numbered lists, the results are the #ID in the lists. I will need to use this line item further to bring in actuals from different module and I assume, I will not be able to do so with the set of #ID from 2 different numbered lists.

 

A bit more background: The list used for column B in the example would be #WL_Cus03_BAM list, which contains all the centers where we sell. Our customers are hospitals and sometimes they group together, to get better pricing conditions. The list A in the example would be Centers-Groups within GPO, here some of the centers from list B are already grouped and the rest of the centers where the grouping is not relevant are blank. In another module, I have actuals per center (from list A) and what I need to do is to be able to somehow bring the actuals to list B, where the actuals for the grouped centers is the sum of the centers within the group. 

 

What I tried to achieve by the exercise in the ADM_06 module (screenshot from previous reply) is, I have there all the centers in one column (Center), then for the centers where grouping is relevant, I have the name of the groups(Final Mapping). The desired result would be to have a column with center names, but where relevant the group name would display for all the centers within that group.

 

The groups might change and are created directly in the model. Each center has a BAM name, which is also the code, but the groups of centers do not have any code.

 

Thank you.

Richard

rob_marshall

@RichardG 

 

So, yes, if you are using a numbered list, that #number really doesn't tell you a hole lot.  With that said, what is the format of the Display Name?  If it is text formatted, you can use that.  So the formula would be:

 

if isnotblank(Column A) then List A.Display Name[Lookup: Column A] else #WL_Cus03_BAM.Display Name[Lookup:Column B]

 

One suggestion, and again, I know very little of what you are trying to do, but see if this might work out for you.  What if all of your centers were in the same list, but you use a Subset (named ss_GPO:List Name)?

 

Ideally, what you would like to do is have a flat list of all your Centers, this can be a numbered list with the Display Name being Text, make sure you have a code (I believe you stated you already have this).  From here, you can create a SYS module storing all of the metadata for the centers.  With this SYS module, you can create a boolean line item for GPO which can either be used for creating a subset or for checking logic (if SYS Module.GPO then something else something else).

 

What is great about doing it this way, if you were to use the centers in an hierarchy, the display name should be formatted back to this flat list (when loading the hierarchy members, map the code of the flat list to the display name formatted to flat list - trust me, it will work).  In doing this, you are leaving "bread crumbs" or links to the flat list from the hierarchy.  Let me know (DM me) if you want to get on a call or if the above is not clear.

 

Rob