How to create a chain b/w 2 Fields

[Ashish]

Hi, I have this sample Data,

Col A Ref_No
0275 0217
0217 0456
0456 0000

How to assign same surrogate key to all of them, since all of them are in link.

0456 is the base number and all base number having Ref_No=0000

Any suggestions will be most welcome.

Thnks.

[Nripendra Chand]

Allocate chain number to each chain and then generate surrogate keys for chain numbers.

[paultechm]

There might be better solution than this ,Try this and let me know


Job 1

Create a job to split the actual file based on the columnB(Ref_No) ,one has all the '0000'records with sequncenumber and other has sorted(desc) not like '0000' records





surrogatekeygen---->seqfileMater.txt(SEQ,ColumnA,ColumnB)
ColumnB='0000'
SeqfileActual--filter
ColumnB<>'0000'
Sort(desc ColumnB)----->seqfileDetail.txt


Job 2

External source with command as (echo #Param1#) ,read the data as a single column(ColumnP) and split into columnA,ColumnB using columnimport.
Join the Master data(0000 .txt) with this ,The master data to be transformed by replacing columnB value with CoulmnA (join requires same name).

Reterive the Sequence number based on columnB and append all the values into master data with corresponding sequence number


seqfileMater.txt>Trnasformer(Replace ColumnB with columnA value)(SEQ,ColumnB)
-

echo #Param1#
External Source--- > columnimport(to columnA,ColumnB)---> join(ColumnB to reterive SEQ)----seqfileMater.txt(append)

Job 3(Sequence)

In order to loop this activity create a sequence based on the detail records(<>0000) ,pass each set of records as a parameter to the JOB2

Command(tr '\n' '~' seqfileDetail.txt)-----loop(with other '~')---Job2(parm1=loopcounterval)-------endloop