String Manipulation of characters and adding Prefix

Good morning,

In the dataset included, I am trying to accomplish the following:

Under Column named “Account Attributes” I have rows with full account numbers that contain 8 characters. The first step I need to accomplish is to have 4 characters only those. For those that get converted to 4 characters, I need to also add a prefix “F” before the numbers. It will look like this “F6681”

Tried different approached under column expressions and adding constant columns and then combining but was not being successful.

Thanks in advance for the support.

Output example.xlsx (45.1 KB)

Hi @lazaronewhall17

In this case I would evaluate if the account attribute consists of 8 chars. If so, concatenate “F” + the first 4 characters of the account attribute. If not, retain the account attribute.

In a Column Expression, you can use:

if (length(column("column1")) == 8) {
    "F" + left(column("column1"), 4)
} else {
    column("column1")
}

If you want a more qualitative analysis of the account attribute, you could opt for a RegexMatcher. For example, if you only want to manipulate the attribute if the last 4 digits are zero, you could use something like:

if (regexMatcher(column("column1"),".*([0]{4}$)") == true) {
    "F" + left(column("column1"), 4)
} else {
    column("column1")
}

Hope this helps!

2 Likes

ArjenEX,

Worked like a charm :slight_smile: Thanks for the support and for the different approaches. It will definitely help in my development using the tool.

Thanks

Laz

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.