Seq Increment

[devanars]

I have a requirement like
id phone
001 9393
001 3232
002 3222
002 4332
002 2213
003 4567
......


i need the target like

id value phone
001 1 9393
001 2 3232
002 1 3222
002 2 4332
002 3 2213
003 1 4567


can anyone help me to how to implement this.

[chulett]

Use stage variables in a transformer to do Group Change Detection and handle the sequence number yourself - set to '1' for each new 'group' and add one while you're still in a group.

Using a Sort stage set to "Don't Sort (Previously Sorted)" with "Create Key Change Column" set to True can make this a little easier.

[devanars]

Sort_23,0: Input not sorted at record 2 is the error coming and job is getting aborted. advise please

[chulett]

It would help if we had a clue what you are doing - sounds like it doesn't think your sorted is already sorted. If you are trying to do the Key Change thing, make sure only your "Id" field is noted as your key field.

If that doesn't work out, skip the sort and just use stage variables:

Code: Select all

Variable   Derivation                           Initial Value
svNewId:   LinkName.Id <> svPrevId              @FALSE 
svSeq:     If svNewId Then 1 Else svSeq + 1     0
svPrevId:  LinkName.Id                          ""

User your Link.FieldName values above and it should work fine for you to use svSeq as the derivation of your Sequence column.

[devanars]

May i know what is this second one. anything missing below logic.
svSeq: If svNewId Then 1 Else svSeq + 1

Please correct me if i am wrong

[ray.wurlod]

The first stage variable (svNewId) determines whether it is a new ID value by comparing the ID value in the current row with the ID value in the previous row (which is stored in svPrevId which has not yet been evaluated in the current row).

The middle stage variable (svSeq) resets the count to 1 if a new ID has been detected or increments the count (itself) otherwise.

[chulett]

Yup, thank you Mr Wurlod and nope, nothing missing below logic.