Providing Unique Reference for new items

[PassION_es]

I have a dataset where each new items will be given a unique reference number. I want the new items to be recorded with a new reference number following the last reference number. Please see below example

Input file:

Existing Record

Name - Reference

Aela  - AM000097

Erza -  AM000098

New Items to be Added in the Record:

Cloud 

Lexy 

Output in Alteryx:

Name - Reference

Aela  - AM000097

Erza -  AM000098

Cloud - AM000099

Lexy - AM000100

Each should have an alpha-numeric characters of upto 8 characters only (AM000000).

Thank you in advance.

[flying008 · Accepted solution]

Hi, @PassION_es 

FYI.

IIF(IsEmpty([Reference]), Left([Row-1:Reference], 2) + PadLeft(ToString(ToNumber(Right([Row-1:Reference], 6)) + 1), 6, '0'), [Reference])

   

[binuacs · Accepted solution]

@PassION_es one way of doing this

[PassION_es]

thank you @flying008 and @binuacs