One Star

How to Reset Auto-Increment / Number Sequencer in tMap?

Hi,
I'm trying to create a unique "Sub-ID" key when dealing with a denormalized data set that has no primary key per record; however there are user IDs.
My Goal is to turn the data from this:
ID|Column
1|a
1|b
2|a
3|a
3|c
...
to this:
ID|Column|SubID
1|a|101
1|b|102
2|a|201
3|a|301
3|c|302
...
But what I'm getting is this:
ID|Column|SubID
1|a|101
1|b|102
2|a|203
3|a|304
3|c|305
...
I'm having trouble figuring out how to get Talend to reset the auto-incrementer/Numeric.sequence in the expression so that it starts back at 1 for each new set of records based on ID.
With the data I'm dealing with, the IDs are integers that are 9 characters long, but I decided to add two zeros at the end making it 11 characters so that I can increment between 0-99 in that space w/out having to run into potential dupes (I had to convert from int to double to break the length limits with the added "00"s):

(((--- In case you can't see, my var code is Double.valueOf(row1.STUDENTID+"00") while my out code is Var.STUDENTID+Numeric.sequence("s1",1,1)---)))
If anyone has any suggestions on this one, it would be greatly appreciated!
Thanks In Advanced (TIA!),
Alex P
4 REPLIES

Re: How to Reset Auto-Increment / Number Sequencer in tMap?

You can do what you need with just the Numeric.sequence method.
use your ID as the "name" of the sequence:
Numeric.sequence(row1.ID,row1.ID,1)
if ID = 10 this will generate sequences that start at 10 and increment by one every time the ID=10 is passed.
if ID = 20 this will generate sequences that start at 20 and increment by one every time the ID=20 is passed.
One Star

Re: How to Reset Auto-Increment / Number Sequencer in tMap?

Hi,
I'm trying to create a unique "Sub-ID" key when dealing with a denormalized data set that has no primary key per record; however there are user IDs.
My Goal is to turn the data from this:
ID|Column
1|a
1|b
2|a
3|a
3|c
...

to this:
ID|Column|SubID
1|a|101
1|b|102
2|a|201
3|a|301
3|c|302
...

But what I'm getting is this:
ID|Column|SubID
1|a|101
1|b|102
2|a|203
3|a|304
3|c|305
...

I'm having trouble figuring out how to get Talend to reset the auto-incrementer/Numeric.sequence in the expression so that it starts back at 1 for each new set of records based on ID.
With the data I'm dealing with, the IDs are integers that are 9 characters long, but I decided to add two zeros at the end making it 11 characters so that I can increment between 0-99 in that space w/out having to run into potential dupes (I had to convert from int to double to break the length limits with the added "00"s):

(((--- In case you can't see, my var code is Double.valueOf(row1.STUDENTID+"00")  while my out code is Var.STUDENTID+Numeric.sequence("s1",1,1)---)))
If anyone has any suggestions on this one, it would be greatly appreciated!
Thanks In Advanced (TIA!),
Alex P

I m also facing the same problem please anyone help me
Fifteen Stars

Re: How to Reset Auto-Increment / Number Sequencer in tMap?

Try this.....
Var.STUDENTID+Numeric.sequence(("s"+Var.STUDENTID),1,1)

This basically creates a new sequence for each STUDENTID and switches between them as the different STUDENTIDs arrive in your dataset. Beware though, in the example given the numbers are limited to only 99. If the STUDENTIDs are numeric, adding two 0's to multiply them by 100 and then adding the sequence value, will mean if there are more than 99 records for a STUDENTID then the number will be a mix up.
Rilhia Solutions
Five Stars

Re: How to Reset Auto-Increment / Number Sequencer in tMap?

Good day 
Please add logic to reset seuence.  Use this :
Numeric.resetSequence("s1",1)
Best regards,
Alfonso Borre
skype : alfonso.borre
aborresarmiento@yahoo.es