How to use FINDITEM for a concatenated string

PrevContributor
PrevContributor
in Modeling

Hi all, I'm a new Anaplan user and have a question regarding the FINDITEM function. I have currently have two numbered lists (1. Account List & 2. Raw Data List) and I would like to use the FINDITEM function in the Raw Data List to match the account ID in the Account List. However, the account ID in the Raw Data list is concatenated, and it's been giving me errors. Example of the concatenated string: 12345_ABC_DEF The account ID on the Account List: 12345 I would like to search the "12345" part in the concatenated string with the FINDITEM function and match with the account ID which is in the Account List. Thanks in advance for your help! Rumi

Tagged:

Answers

  • peter_mcanena
    peter_mcanena
    Hi Rumi,

    The following formula should work:
    FINDITEM(Account, LEFT(Concatenated String, FIND("_", Concatenated String) - 1))
    This finds the section of the concatenated string up to the first underscore, then looks that up in the codes of the Account numbered list. It will work as long as you've got codes in your Account list, and "12345" is the correct code.

    Hope this helps,

    Pete
  • PrevContributor
    PrevContributor
    Hi Peter,

    Thanks for the advise!
    I tried this formula and it goes through, however it returns a blank value.

    The code that I want to retrieve from the account list (1234) in the concatenated string is actually in the middle of the concatenated string like below.

    Example of the concatenated string: ABC_DEF_GHI_1234_JKL_MN
    The account ID on the Account List: 12345

    I tried to change the last "-1" to "-3" to lookup to the third underscore, but it still does not work.
    FINDITEM(Account, LEFT(Concatenated String, FIND("_", Concatenated String) - 3))

    Your kind help will be appreciated!

    Thanks,
    Rumi
  • PrevContributor
    PrevContributor
    Hi Rumi,

    Does the ID always fall the exact number of characters within the concatenated string?  Or is there always a fixed number of underscores before the ID appears?  If not, is the ID always numeric with the rest of the concatenated string always alphabet letters?  Basically just looking for any structure to the concatenated string, we can help you create your FIND string based on any of those rules if you can provide any more details around the structure.   

    Also, something that may help you troubleshoot in the meantime would be to separate out the FIND part of the formula into a separate line item until you know you have it working (mainly to answer the question, is the FIND function causing the problem or the FINDITEM function?).  

    Hope that helps, looking forward to more details here!

    -Chris Weiss
  • Jaakko
    Jaakko
    Hi Rumi,

    Like Chris said, I would also start debugging the finditem by separating MID function to separate line item to see what string MID produces (and also separate line items for parameters given to that to see where the error is).

    Here is a bit more to add to this. The idea is the same what Peter suggested. This uses MID instead of LEFT if the code is in the middle of the string. What I'd like to add here is how to find the code from pattern "ABC_DEF_GHI_1234_JKL_MN" where I assume that the code is always between 3rd and 4th occurrences of underscore.

    You  need these two functions:

    MID is a function for substrings in anaplan. It requires three parameters
    - The text to get substring from (concatenated string)
    - Start character (which is the next character from the third occurrence of underscore)
    - Number of characters (which is the difference between the 3rd occurrence of underscore and 4th occurrence of underscore. In fact, minus one of this difference if you really think about it)

    FIND function also requires three parameters
    - String or character to search (underscore)
    - Text to search from (concatenated string)
    - Character index to start search from

    Three nested FINDs are needed in order to find the third occurrence of underscore. Your account code starts from the character after that. First FIND starts from beginning of the concatenated string, second starts from character after the first underscore and third starts from character after the second underscore. You will also need the 4th to count the length of the account code in case it's not always the same.

    You can find the 1st occurrence of underscore with part of Peter's formula (does not include starting character)
    1st Occurrence = FIND("_", Concatenated String)

    2nd Occurrence = FIND("_", Concatenated String, 1st occurrence +1) = FIND("_",Concatenated String,FIND("_", Concatenated String)+1)

    3rd Occurrence = FIND("_", Concatenated String, 2nd occurrence +1) = FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)

    4th Occurrence: FIND("_", Concatenated String, 3rd occurrence +1) = FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1)

    Length of substring = (4th occurrence - 3rd occurrence - 1) = FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1) - FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)-1

    If the account code is always of the same length, you can replace the length with a constant value.

    Substring:
    MID(text,start,length) = MID(Concatenated String,
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1,
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1)-
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)-1
    )

    Finditem:
    FINDITEM(Account,
    MID(Concatenated String,
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1,
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)+1)-
    FIND("_",Concatenated String,FIND("_",Concatenated String,FIND("_", Concatenated String)+1)+1)-1
    )
    )

    I hope this helps to get the idea. I also hope that my formulas don't include typing errors. 😉 Maybe this is something Anaplan could add to FIND formula. A parameter to tell the occurrence to find.
      
  • knitish
    knitish

    What if concatenated string had been a list in here. 

Categories