Generate rows

I am having a hard time using generate rows/multi row tool to increment the values by 1 and not sure where I'm going wrong. Here is the scenario. One of the column in a data set is Alert ID which is of the format AE100, AE101,AE102 etc. I have to take the maximum of this and add a new column in the output file where the value should be incrmented by 1. For example, if the max value is AE102 then in the new column the value should begin as AE103 and continue as AE104,AE105 till the last value of account id. Any help on this would be appreciated. Thank you in advance!

Input

Account ID Alert ID

1 AE100

2 AE101

3 AE102

4 AE103

5 AE104

After using the summarize tool, the max value will be AE104

Desired output

Account ID New_Alert

1 AE105

2 AE106

3 AE107

4 AE108

5 AE109

Developer Tools

Accepted answers

fmvizcaino

Hi @aparna0208 ,

Here is a suggestion for you.

I'm using the max function to retrieve the max Alert and then using regex_replace to separate the number from the alert - it would be also possible to use the right function, but since I'm not sure it you can have more than 3 digits, I'm using regex.

Best,

Fernando Vizcaino

2020-06-01 Generate rows.yxmd

fmvizcaino

Hi @aparna0208 ,

I'm sharing one detailed example showing each step of the calculation, also including the left 0 in your new alert ID

Best,

Fernando Vizcaino

2020-06-01 Generate rows.yxmd

All comments

fmvizcaino

Hi @aparna0208 ,

Here is a suggestion for you.

Best,

Fernando Vizcaino

2020-06-01 Generate rows.yxmd

aparna0208

@fmvizcaino Awesome. This gave me the desired output. Thank you so much:) If you don't mind could you please explain 1) purpose of record id tool and 2) tonumber(REGEX_Replace([Max_Alert ID], '(.*?)(\d+)', '$2'))+[RecordID] - what exactly does this expression do?

Just to have better understanding of these tools and formula

Thank you!

aparna0208

Sorry I just noticed something. When the number starts with zero it gets trimmed. For example, if max value is AE01730 then the value in new column should be AE01731 but in this case the zero gets trimmed and output looks like AE1731. @fmvizcaino

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703