Assigning Seq Number for each group
Posted: Thu Jun 01, 2006 11:12 am
Hi Everybody,
I need to assign Sequence Number for transactions done on a paritcular day.
My Input File
ID Prcs_Dt Field1 Field 2 Feild3 In_Date Amt
2001 20060601 ABC 123 ABC156 20060501 200.00
2002 20060601 ABC 123 ABC156 20060501 1000.00
2003 20060601 ABC 123 ABC156 20060501 5000.00
2004 20060601 ABC 123 ABC156 20060502 4500.00
2005 20060601 ABC 123 ABC123 20060501 300.00
2006 20060601 ABC 123 ABC123 20060501 3000.00
2007 20060601 ABC 123 ABC123 20060501 5000.00
2008 20060601 ABC 123 ABC123 20060511 6500.00
2009 20060601 ABC 123 ABC123 20060511 200.00
2010 20060601 ABC 123 ABCXYZ 20060521 1000.00
2011 20060601 ABC 123 ABCXYZ 20060521 5000.00
2012 20060601 ABC 123 ABCXYZ 20060522 4500.00
2013 20060601 ABC 123 ABCXYZ 20060529 5000.00
2014 20060601 ABC 123 ABCXYZ 20060529 4500.00
The output file needs to generate seq number for each group and the ID and PRCS_DT and Seq_Num are key. So the target should look like this
ID Prcs_Dt Feild3 In_Date Amt Seq_Num
2001 20060601 ABC156 20060501 200.00 001
2002 20060601 ABC156 20060501 1000.00 002
2003 20060601 ABC156 20060501 5000.00 003
2004 20060601 ABC156 20060502 4500.00 001
2005 20060601 ABC123 20060501 300.00 001
2006 20060601 ABC123 20060501 3000.00 002
2007 20060601 ABC123 20060501 5000.00 003
2008 20060601 ABC123 20060511 6500.00 001
2009 20060601 ABC156 20060511 200.00 002
2010 20060601 ABCXYZ 20060521 1000.00 001
2011 20060601 ABCXYZ 20060521 5000.00 002
2012 20060601 ABCXYZ 20060522 4500.00 001
2013 20060601 ABCXYZ 20060529 5000.00 001
2014 20060601 ABCXYZ 20060529 4500.00 002
I went through posts earlier
Is there a simple way to do this?
This is how I tried
Sort the input file based on ID, Field1, Field2, Field3 and In_Date
S
O ---------> Xfm1------> Xfm3--------->Hash1
^
U |
|
R |
|
C---------> Xfm2 -------> Hash1
E
Here I am using a dynamic lookup
Xfm2 has a constraint of @OUTROWNUM = 1
Both the hash are same
the key for the hash is ID, Prcs_DT, Field3, In_date and Seq_Num
At first I am populating 001 for Seq_Num in the first hash
Then in the lookup in the Xfm3, I am checking if there is a match and checking if all the field is equal and then incrementing the seq_num
I know I am complicating things
I havent acheived the solution
Is there an easy method to do this?
Thanks
I need to assign Sequence Number for transactions done on a paritcular day.
My Input File
ID Prcs_Dt Field1 Field 2 Feild3 In_Date Amt
2001 20060601 ABC 123 ABC156 20060501 200.00
2002 20060601 ABC 123 ABC156 20060501 1000.00
2003 20060601 ABC 123 ABC156 20060501 5000.00
2004 20060601 ABC 123 ABC156 20060502 4500.00
2005 20060601 ABC 123 ABC123 20060501 300.00
2006 20060601 ABC 123 ABC123 20060501 3000.00
2007 20060601 ABC 123 ABC123 20060501 5000.00
2008 20060601 ABC 123 ABC123 20060511 6500.00
2009 20060601 ABC 123 ABC123 20060511 200.00
2010 20060601 ABC 123 ABCXYZ 20060521 1000.00
2011 20060601 ABC 123 ABCXYZ 20060521 5000.00
2012 20060601 ABC 123 ABCXYZ 20060522 4500.00
2013 20060601 ABC 123 ABCXYZ 20060529 5000.00
2014 20060601 ABC 123 ABCXYZ 20060529 4500.00
The output file needs to generate seq number for each group and the ID and PRCS_DT and Seq_Num are key. So the target should look like this
ID Prcs_Dt Feild3 In_Date Amt Seq_Num
2001 20060601 ABC156 20060501 200.00 001
2002 20060601 ABC156 20060501 1000.00 002
2003 20060601 ABC156 20060501 5000.00 003
2004 20060601 ABC156 20060502 4500.00 001
2005 20060601 ABC123 20060501 300.00 001
2006 20060601 ABC123 20060501 3000.00 002
2007 20060601 ABC123 20060501 5000.00 003
2008 20060601 ABC123 20060511 6500.00 001
2009 20060601 ABC156 20060511 200.00 002
2010 20060601 ABCXYZ 20060521 1000.00 001
2011 20060601 ABCXYZ 20060521 5000.00 002
2012 20060601 ABCXYZ 20060522 4500.00 001
2013 20060601 ABCXYZ 20060529 5000.00 001
2014 20060601 ABCXYZ 20060529 4500.00 002
I went through posts earlier
Is there a simple way to do this?
This is how I tried
Sort the input file based on ID, Field1, Field2, Field3 and In_Date
S
O ---------> Xfm1------> Xfm3--------->Hash1
^
U |
|
R |
|
C---------> Xfm2 -------> Hash1
E
Here I am using a dynamic lookup
Xfm2 has a constraint of @OUTROWNUM = 1
Both the hash are same
the key for the hash is ID, Prcs_DT, Field3, In_date and Seq_Num
At first I am populating 001 for Seq_Num in the first hash
Then in the lookup in the Xfm3, I am checking if there is a match and checking if all the field is equal and then incrementing the seq_num
I know I am complicating things
I havent acheived the solution
Is there an easy method to do this?
Thanks