IBM Sterling Transformation Extender

Sterling Transformation Extender

Come for answers, stay for best practices. All we're missing is you.


#Sterling
#Supplychain
 View Only
Back to discussions
Expand all | Collapse all

How to generate sequence number for each record type (complex rec types)

  • 1.  How to generate sequence number for each record type (complex rec types)

    Posted 03/17/10 01:58 PM

    Originally posted by: Kiran Kumar Reddy


    I have a requirement to assin a sequence number to every record based on a logic -

    My input file (Fixed width file) has 2 record types
    Master Record
    Detail Record

    The ID field is the first field in these records. ID (master record) is the primary key, ID (detail record) is the foreign key.
    My output file (Fixed width file) has 3 record types

    master Record
    detail record
    sub_detail record

    And again the ID field is the key, which is the first field.

    For every master, there are multiple details and for every details there are multiple sub details

    Ex: File structure

    Master for ID 100
    Detail for ID 100
    Subdetail for ID 100
    Subdetail for ID 100
    Subdetail for ID 100
    Detail for ID 100
    Subdetail for ID 100
    Subdetail for ID 100
    Master for ID 111
    Detail for ID 111
    Subdetail for ID 111
    Detail for ID 111
    Subdetail for ID 111
    Subdetail for ID 111

    Now I need to assign (APPEND TO THE END OF RECORD AS A FIELD) a sequence number for every master, detail and sub detail records in the following manner

    RECORD TYPE ID SEQUENCE_ID
    Master 100 1
    Detail 100 1
    Subdetail 100 1
    Subdetail 100 2
    Subdetail 100 3
    Detail 100 2
    Subdetail 100 1
    Subdetail 100 2
    Master 111 2
    Detail 111 1
    Subdetail 111 1
    Detail 111 2
    Subdetail 111 1
    Subdetail 111 2

    For every new master record (here we have 2 master recs - ID 100 and 101), Sequence_ID should increment by 1 starting with 1.

    For every detail record inside a master record the sequence id again should be incremented by 1 starting with one. The sequence id should be reset (to start from 1) for every ID field.

    For every sub_detail record inside a detail record the sequence id again should be incremented by 1 starting with one. The sequence id should be reset (to start from 1) when the next detail record is encounterd.

    Is there a logic to create the sequence number like this?

    Please see the attached spread sheet for better understanding of the data.
    #IBMSterlingTransformationExtender
    #DataExchange
    #IBM-Websphere-Transformation-Extender


  • 2.  Re: How to generate sequence number for each record type (complex rec types)

    Posted 03/17/10 05:05 PM

    Originally posted by: SystemAdmin


    From what i can understand from your explaination, assuming you are already getting the desired output (minus) the seq number or occurrence for every rec in master , dtl & sdtl record types,

    just add the additional type blob in your op type structure & use Index function to get the index of every record that you are passing inside the f_map ( & at every level) , to get the index for master , dtl & sdtl , & then map this field to the blob that you have added previously.
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange


Related Content