topic Re: how to generate numeric sequence based up on id in Talend Studio

how to generate numeric sequence based up on id

Anonymous — Sat, 04 Feb 2017 02:19:25 GMT

i have a data with
id,name,state
1,john,oh
2,terry,ca
3,peater,pa
4,mat,tx
5,steven,oh
6,cathy,ca
7,peater,pa
8,mat,tx
9,tracy,ok
now i am trying to get
id,name,state,rowid
1,john,oh,1
2,terry,ca,1
3,peater,pa,1
4,mat,tx,2
5,steven,oh,2
6,cathy,ca,2
7,peater,pa,3
8,mat,tx,3
9,tracy,ok,3
here i am trying to insert a row id for every 3 rows ie for every three rows i need have same row id and then increment the number for next 3 rows.
can you suggest what exactly do i need to do here

Re: how to generate numeric sequence based up on id

Anonymous — Sat, 04 Feb 2017 02:50:31 GMT

Hi
Link the component to a tJavaRow, and write the java code as below:

if(input_row.id%3==1){
output_row.seq_id=Numeric.sequence("s2",1,1);
globalMap.put("last_seq_id",output_row.seq_id);
}else{
output_row.seq_id=(Integer)globalMap.get("last_seq_id");
}

//seq_id is a new column in the output table.
Regards
Shong

Re: how to generate numeric sequence based up on id

Anonymous — Mon, 06 Feb 2017 14:56:52 GMT

Hi Shong,
I have a scenario just like this. I have two two tables which share a Address column between them. I want to generate a Sequence_ID (like a foreign key referencing Addresses), which would link one table to the other table based on the Addresses column. Table A has all Addresses value possible while Table B has only the Addresses value pertinent to our business.
Table A Table B
( Numeric.sequence("s1",1,1)) | (Requested from you)
ID_in Table_A Addresses | ID_in Table_B(connecting to Table A) Addresses

1 1452 Talend Road | 1 1452 Talend Road
2 1000 ETL Drive | 1 1452 Talend Road
3 4585 Data Road | 3 4585 Data Road

My current job is:

csv file -------------------tMap---------------------postgreSQLoutput (Table A)

csv file -------------------tMap---------------------postgreSQLoutput ( Table B)

How can I generate a key for Table B that it will reference or search by the ID in Table A (like many to one ( ID number 1) or one to one ( ID number 3 relationships)?
Thanks,
CM

Re: how to generate numeric sequence based up on id

Anonymous — Tue, 07 Feb 2017 03:12:26 GMT

Hello Colleen
After you generate a sequence id for each address in table A, you can do an inner join based on address column between source data and Table A to generate the ID in tables, refers to this KB article, the job looks like:
csv file ------main-------------tMap----------main-----------postgreSQLoutput (Table A)
|
onsubjobok
|
csv file -------main------------tMap----main-----------------postgreSQLoutput (Table B)
|
lookup
|
table A

Regards
Shong

Re: how to generate numeric sequence based up on id

Anonymous — Wed, 08 Feb 2017 20:30:09 GMT

Thanks Shong,
csv file ------main-------------tMap----------main-----------postgreSQLoutput (Table A)
|
onsubjobok
|
csv file -------main------------tMap----main-----------------postgreSQLoutput (Table B)
|
lookup
|
table A
This works great but there is something that baffles me here. I inserted a (Numeric.sequence("s1",1,1)) for Table A and then did a inner join in Table B with Table A on Address values. When I inserted a (Numeric.sequence("s1",1,1)) for Table B separately, the sequence started with 2025 not 1, as I had initially expected.
Table A has 2024 values and (Numeric.sequence("s1",1,1)) starts at 1 and ends at 2024 for Table A ---------- I fully comprehend this.
Table B has 1500 values and (Numeric.sequence("s1",1,1)) starts at 2025 and ends at 3525 for Table B ------ I do not comprehend this.
How can I get (Numeric.sequence("s1",1,1)) for Table B to start at 1 rather than 2025.
Thanks,
CM

Re: how to generate numeric sequence based up on id

Anonymous — Thu, 09 Feb 2017 01:52:59 GMT

You should define another sequence name for table B, the first parameter of function is the sequence name.
(Numeric.sequence("s2",1,1))