topic Re: Remove everything before last _ in given field name in Alteryx Designer Desktop Discussions

Remove everything before last _ in given field name

IJH34 — Thu, 13 Jun 2024 21:45:54 GMT

I have a number of fields that were joined together, and thus renamed with various "left_" "right_" before the field name.

e.g.

_left_left_right_field 1

_left_right_field 2

_left_right_right_field 3

what I would like to do, possibly with the combination of dynamic rename and regex, is remove anything prior to and including the last _ in any given field header.

e.g.

field 1

field 2

field 3

Re: Remove everything before last _ in given field name

LordNeilLord — Tue, 21 May 2019 16:39:35 GMT

Hey @IJH34

Not sure its the most efficient method but I like to use reversestring...

ReverseString(Left(ReverseString([Field1]), FindString(ReverseString(Field1), "_")))

Neil

Re: Remove everything before last _ in given field name

Thableaus — Tue, 21 May 2019 16:43:47 GMT

Hi @IJH34

Here's a good regex:

REGEX_REPLACE([FieldName], ".*_(.*)", "$1")

Cheers,

Re: Remove everything before last _ in given field name

danilang — Tue, 21 May 2019 16:46:15 GMT

And another one

REGEX_Replace([Field1], "(.*_)+", "")

Dan

Re: Remove everything before last _ in given field name

IJH34 — Tue, 21 May 2019 18:18:25 GMT

@Thableaus Perfect! Regex is a tricky one for me.

Re: Remove everything before last _ in given field name

MarqueeCrew — Tue, 21 May 2019 20:52:13 GMT

@IJH34,

Back in the day before I knew what RegEx was, I would solve this type of problem with string functions. While not as fancy as pattern matching, it runs faster. In fact, with 30 million records it takes 48.4 seconds to calculate your results using RegEx (quite fast!) and only 37.5 seconds to use the following formula:

Right( [Field1], MIN( FindString(ReverseString([Field1]), "_thgir"), FindString(ReverseString([Field1]), "_tfel") ) )

Bottom line is that you should decide which solution is more easily explained to your team and use RegEx when you're comfortable with it. I do encourage you to learn it. @danilang and @Thableaus gave great RegEx advice and @LordNeilLord sent you down the same path that I would have taken you on. All of the suggestions in the post are solution worthy.

Cheers,

Mark

Re: Remove everything before last _ in given field name

LordNeilLord — Wed, 22 May 2019 07:38:57 GMT

I totally agree @MarqueeCrew

If you don't understand it....don't use it (or learn it quickly)