[resolved] Generate unique key from first and last name + seq. no. (as needed)

[dflansburg]

In Talend what is the best way to generate a unique key from the first character of the first name + 3 first characters of the last name + integer in order to prevent duplication. I can generate the Alias key using string functions in my tMap, but do not know how to add sequence number if needed. 

The WorkerId is the unique key used by the software, the alias is intended to be a more user-friendly and generated off first and last names.  

# SAMPLE DATA INPUT (first row header) 
'WorkerId','FirstName','LastName','AliasCandidate' 
'1080','Carl','Smith','CSMI' 
'6774','Charlie','Smith','CSMI' 
'6787','Carol','Smith','CSMI' 
'6327','Carl','Wright','CWRI' 
'4619','Carol','Wrigley','CWRI' 
'6767','Chris','Wright','CWRI' 

# DESIRED RESULT SET (first row header) 
'WorkerId', 'Alias' 
'1080', 'CSMI' 
'6774', 'CSMI1' 
'6787', 'CSMI2' 
'6327', 'CWRI' 
'4619', 'CWRI1' 
'6767', 'CWRI2'

I am new to Talend and unsure what it means when support says: "You could do a lookup in the tmap and then set the expression based on the lookup." 
Please advise.

[Anonymous · Accepted solution]

This is quite straight forward. Lets assume your alias is sent to your tMap with a row named row1 and a column called alias. Therefore in the tMap you would refer to it as row1.alias.

What you will need to do is to create an output column called "AliasCandidate" and add the following value expression....

row1.alias + routines.Numeric.sequence(row1.alias, 1, 1)

The above will append a sequence number which is tied to the alias. So for the following dataset of alias', you would get the following AliasCandidates....

CSMI, CSMI1 
CADB, CADB1
CADB, CADB2
CARF, CARF1
CSMI, CSMI2
CADB, CADB3
CADB, CADB4
CARF, CARF2
CARF, CARF3

[dflansburg]

That did help. I was able to work with our CSM and co-work Java developer and put something similar to use:

First I am getting all existing Alias values, using a tSort to stage for sequence and then parsing out Alias and the next sequence number:

int code_num = 0; String code_alpha = input_row.Alias.substring(0,4); 
if ( input_row.Alias.length() > 4 ) { code_num = Integer.parseInt( input_row.Alias.substring(4, input_row.Alias.length() ) ); } 
Numeric.sequence(code_alpha, code_num, 1);

The next step uses the max value and increments by 1 via a tMap:

Numeric.sequence(row4.Alias,1,1)

The original preference was to not append an integer if the alias did not previously exist. Being green with Java and wanting to get on to other items, I decided to append "1" to the every first seen Alias.

Thanks,
David