Generate rows

[aparna0208]

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

[fmvizcaino · Accepted solution]

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

[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 

[fmvizcaino · Accepted solution]

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

[aparna0208]

It worked:) Thanks a lot for your inputs!@fmvizcaino