Originally posted by: Kiran Kumar Reddy

Hi,

I have a scenario where I would need to generate a sequence number for every record based on the logic below -

1. The file would contain an ID field (this is NOT a unique key).
2. We will have multiple records for each id.
3. For each record we will need to generate a SEQUENCE ID which is more like a running number. This sequence number needs to be reset for every ID (i;e. whenvere there is a change in the value in the ID field)

Ex: The field1 in the below mentioned data is the ID, and the field to be generated is the field2 which is the sequence ID.
1,1,O,C,C1,Level 3,CLASS3,MN
1,2,O,C,C1,Level 3,CLASS3,MN
1,3,O,C,C1,Level 3,CLASS3,MN
1,4,O,C,C1,Level 3,CLASS3,MN
1,5,O,C,C1,Level 3,CLASS3,MN
1,6,O,C,C1,Level 3,CLASS3,MN
1,7,O,C,C1,Level 3,CLASS3,MN
1,8,O,C,C1,Level 3,CLASS3,MN
2,1,I,S,C2,Level 1,CLASS1,NH
2,2,I,S,C2,Level 1,CLASS1,NH
2,3,I,S,C2,Level 1,CLASS1,NH
2,4,I,S,C2,Level 1,CLASS1,NH
3,1,O,C,C1,Level 3,CLASS3,NH
3,2,O,C,C1,Level 3,CLASS3,NH
3,3,O,C,C1,Level 3,CLASS3,NH
3,4,O,C,C1,Level 3,CLASS3,NH
3,5,O,C,C1,Level 3,CLASS3,NH
3,6,O,C,C1,Level 3,CLASS3,NH
3,7,O,C,C1,Level 3,CLASS3,NH
3,8,O,C,C1,Level 3,CLASS3,NH
3,9,O,C,C1,Level 3,CLASS3,NH
3,10,O,C,C1,Level 3,CLASS3,NH
3,11,O,C,C1,Level 3,CLASS3,NH
3,12,O,C,C1,Level 3,CLASS3,NH
3,13,O,C,C1,Level 3,CLASS3,NH

I have put the data into excel for better understanding. PFA.
Please share your thoughts if you have ideas on achieving this

Thanks for your help.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: Kiran Kumar Reddy

Also, Please note that in the given example, the sequence number is reset to 1, when the ID field value changes.

Thanks!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

can you post your ip file , ip tt & expected op of how it should look like...
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: Durairaj

It is posible to generate the sequence number if you can split the group based on the sequence ID to which we need to genarate sequence number.
I have split the group by the rule ID:$=ID:$LAST In type tree so that it will take the records which has same sequence number as a group.

while creating the functional map i have passed the index of the input record as a parameter. This index is mapped as ID.
I have attached the source code here.
I have give the input as
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
1,O,C,C1,Level 3,CLASS3,MN
2,I,S,C2,Level 1,CLASS1,NH
2,I,S,C2,Level 1,CLASS1,NH
2,I,S,C2,Level 1,CLASS1,NH
2,I,S,C2,Level 1,CLASS1,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH
3,O,C,C1,Level 3,CLASS3,NH

Map generated the following output
1,1,O,C,C1,Level 3,CLASS3,MN
1,2,O,C,C1,Level 3,CLASS3,MN
1,3,O,C,C1,Level 3,CLASS3,MN
1,4,O,C,C1,Level 3,CLASS3,MN
1,5,O,C,C1,Level 3,CLASS3,MN
1,6,O,C,C1,Level 3,CLASS3,MN
1,7,O,C,C1,Level 3,CLASS3,MN
1,8,O,C,C1,Level 3,CLASS3,MN
2,1,I,S,C2,Level 1,CLASS1,NH
2,2,I,S,C2,Level 1,CLASS1,NH
2,3,I,S,C2,Level 1,CLASS1,NH
2,4,I,S,C2,Level 1,CLASS1,NH
3,1,O,C,C1,Level 3,CLASS3,NH
3,2,O,C,C1,Level 3,CLASS3,NH
3,3,O,C,C1,Level 3,CLASS3,NH
3,4,O,C,C1,Level 3,CLASS3,NH
3,5,O,C,C1,Level 3,CLASS3,NH
3,6,O,C,C1,Level 3,CLASS3,NH
3,7,O,C,C1,Level 3,CLASS3,NH
3,8,O,C,C1,Level 3,CLASS3,NH
3,9,O,C,C1,Level 3,CLASS3,NH
3,10,O,C,C1,Level 3,CLASS3,NH
3,11,O,C,C1,Level 3,CLASS3,NH
3,12,O,C,C1,Level 3,CLASS3,NH
3,13,O,C,C1,Level 3,CLASS3,NH

I hope it will help you.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: Kiran Kumar Reddy

Hello ASK & Durairaj,

Sorry to respond late. Couldnt watch my thread since I was help up with work.

I was able to achieve this functionality by creating another group abouve my file level.

The highest group is Loop
Followed my File & Record

I used 2 functional map.
Calling the f_map1 (f_map1 call made at File level) by passing the unique id and the complete file.

From the f_map2 (f_map2 call made at the record level) by passing the extract of all records for that particular unique id and the INDEX($)

With this, for each id I was able to reset the Index value.

Thanks for all your help.
ASK/NANCY - you guys were really of great help.
So as Durai too. Thanks.

- Kiran
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender