Providing Unique Reference for new items

Question

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 Answer

Hi, @PassION_es

FYI.

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

binu_acs · Accepted Answer

@PassION_es one way of doing this