how to generate numeric sequence based up on id

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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 

[Anonymous]

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