Issue: Combine 2 columns

RichardG
edited February 2023 in Modeling

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 AColumn BResult
Adam K.AdamAdam K.
Daniel S.DanielDaniel S.
Peter M.PeterPeter M.
 MartinMartin
 AnnaAnna
 EmilyEmily

 

How can this be achieved in Anaplan please?

 

Thanks a lot,

Richard

Answers

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

  • 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:

    RichardG_0-1608658118059.png

    Any suggestion how to approach this?

     

    Thank you!

     

    Best,

    Richard

     

  • @RichardG 

     

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

  • Hi @rob_marshall ,

     

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

     

    RichardG_0-1608671918479.png

    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

  • @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 B)

     

    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 B)

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

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