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.
How to create a chain b/w 2 Fields
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
Re: How to create a chain b/w 2 Fields
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
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