Assigning Seq Number for each group
Moderators: chulett, rschirm, roy
Assigning Seq Number for each group
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
Yeah I understand that, but how do we do this
I was looking at the routine
Can I just modify this routine to take multiple argument?
I know someone has already done this
It would be great if someone gives me more info
Thanks
I was looking at the routine
But how do i use it to check multiple fieldsRowProcCompareWithPreviousValue
Can I just modify this routine to take multiple argument?
I know someone has already done this
It would be great if someone gives me more info
Thanks
Sort by the id the way you are doing and then just check if the present In_Date is equal to the previous In_Date then increment the Sequence number by 1 else start from 001.
Something like
In the output derivation of the Seq_Num put
That should take care of it
Something like
Code: Select all
SeqNum:If In_Date = PrevInDate or @INROWNUM = 1 then SeqNum+1 else 1
PrevInDate: In_Date
Code: Select all
If Len(SeqNum) <3 then FMT(SeqNum,"3'0'R") else SeqNum
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Yes it stores the previous value. It has to be in the order i specified. Only then it will work. And the @INROWNUM = 1 will take care of the first row where you dont have anything to compare against.
Also i failed to see you have other checks other than just IN_Date. Just specify a stage variable for each of the coloumns you want to check. make sure all of these stage variables are after the SeqVar.
And in the SeqVar just add to the if then else statement. That should cover it.
Also i failed to see you have other checks other than just IN_Date. Just specify a stage variable for each of the coloumns you want to check. make sure all of these stage variables are after the SeqVar.
And in the SeqVar just add to the if then else statement. That should cover it.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
@INROWNUM just takes care of the first incoming row where you dont have anything to compare against.
As for reseting the sequence number. Look at the if then else statement
when the condition is not satisfied, it specifies 1 to SeqNum which in other words is reseting the seq.
As for reseting the sequence number. Look at the if then else statement
Code: Select all
SeqNum:If In_Date = PrevInDate or @INROWNUM = 1 then SeqNum+1 else 1
PrevInDate: In_Date
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.