Extracting parts from a series of underscores like 448_2021_2_2021_3_1_56

HarunT
HarunT
in Modeling

Hello All, 

 

I am having trouble extracting certain parts from the code like 217_2021_2_2021_3_1_56, that first three digits is the phase code, then year, then month, then another year, another month, version, and location code. Month and version numbers could be one or two digits depending on the date, so not sure about the best and easiest to extract each information in a separate line item. Any help is extremely appreciated. 

Tagged:

Best Answer

  • MarkWarren
    MarkWarren
    Answer ✓

    If the string has a fixed number of characters every time, you always have 3 characters, then 4 etc.
    Set those positions in a constants system module and use MID. Don't find the positions by calculation as they will always be the same if the string format is fixed.

    Also, your code has a date in it, this goes against best practice. It is better create that code to define the uniqueness of the item and dimension that by time. Hopefully this is part of that process...
    See this article by @rob_marshall → Data hubs: purpose and peak performance - Anaplan Community

Answers

  • bhatjaved
    bhatjaved

    @HarunT You may have to create multiple line items to extract each record separately with the help of Left/Right/Mid separator functions alternatively - It cannot be extracted in one line item as each record will be used for further calculations 

  • alexpavel
    alexpavel

    @HarunT  You can use the FIND function in order to identify where are the underscore characters. Like below.. 

     

    alexpavel_0-1648670839550.png

     

     

    And then...using MID function you can extract in other line-items the phase, code, etc..

     

    Hope it helps

    Alex

  • HarunT
    HarunT
    Thank you Mark. Although the number of characters are not fixed, I believe I figured out the solution. Thanks.

Categories