I am trying to increment the value of an id column which is based on value from another column after joining it to the input file. I am trying to implement this operation in tmap. So the structure is something like this:
Main_file
column 1
column 2
column 3
Look up 1
id
column 2
I am doing a left outer join on column 2 from input file and column 2 from look up table and trying to increment the value only for unique values.
Using the following routine to populate the output column:
Var: Numeric.sequence("s2",(lkp1.id == null)?1:lkp1.id+1,1)
In output col: Relational.ISNULL(lkp1.col2) || lkp1.col2.equals("") ? Var.var1 : lkp1.id
I am getting a different id for duplicate values in column 2 by doing this. However, what i need is unique id for unique column 2 value and same id for duplicate values. Any help on this will be much appreciated.
