Data360 Analyze

 View Only

  • 1.  Find special character and replace it and everything after it

    Posted 09-28-2023 14:23
    Edited by Mike Spalding 09-28-2023 14:43

    Hello,

    I'm working with some data where a PIPE "|" has been placed in the middle of the data.  I'd like to remove the PIPE along with everything after it.

    Example

    Field Name is ProtocolNumber

    Content in field is M111-1234|2023-01-01

    I want to remove the PIPE and the characters after the pipe, leaving only the M111-1234.

    Can this be done using the trim fields node?

    Thanks

    Mike

    PS. I should add that the characters leading up to the PIPE are random in length.  They are not always in the format I gave as my example.



    ------------------------------
    Mike Spalding
    Abbvie Inc.
    ------------------------------



  • 2.  RE: Find special character and replace it and everything after it

    Employee
    Posted 09-29-2023 03:04

    You could use a split in the Transform node, split on pipe and use the first part of the split 



    ------------------------------
    Steve Linsley
    Director International Services
    Precisely Software Inc.
    NAPERVILLE IL
    ------------------------------



  • 3.  RE: Find special character and replace it and everything after it

    Employee
    Posted 09-29-2023 03:56

    As Steve said, this would work:

    out1.ProtocolNumber = in1.ProtocolNumber.split('|')[0]



    ------------------------------
    Jay Reilly
    Precisely Software Inc.
    ------------------------------

    Original Message


  • 4.  RE: Find special character and replace it and everything after it

    Posted 02-13-2024 15:29

    Thanks Jay and Steve, this worked!  I appreciate the help.



    ------------------------------
    Mike Spalding
    manager
    AbbVie Inc.
    ------------------------------

    Original Message


  • 5.  RE: Find special character and replace it and everything after it

    Posted 09-29-2023 04:35

    You could also solve it with regexp in transform node:

    import re
     
     
     
    x = re.search(r'(.*)\|',in1.ProtocolNumber)
     
     
     
    if x:
        
        out1.ProtocolNumber = x.group(1)



    ------------------------------
    Henrik B
    E.ON Sverige
    ------------------------------



  • 6.  RE: Find special character and replace it and everything after it

    Employee
    Posted 09-29-2023 07:44

    The "Trim Fields" node requires specifying the specific set of characters that will be removed, and it removes all of them.  This is not aligned with what I understand to be the result you want.

    You could solve it using the find() method and slicing:

    In a Transform node it might look like this:

    pipePosition = ProtocolNumber.find("|")
result = ProtocolNumber[:pipePosition]

    In a :Calculate Fields" node you might do it all at once with this expression, ProtocolNumber[:ProtocolNumber.find("a")]



    ------------------------------
    Ernest Jones
    Precisely Software Inc.
    PEARL RIVER NY
    ------------------------------