Assigning Seq Number for each group

[ak77]

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

[kumar_s]

Or in simple, maintain a stage variable to check the key change on the sorted data and assign the increased SeqNo to a column.

[ak77]

Yeah I understand that, but how do we do this
I was looking at the routine

RowProcCompareWithPreviousValue

But how do i use it to check multiple fields
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

[DSguru2B]

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

Code: Select all

SeqNum:If In_Date = PrevInDate or @INROWNUM = 1 then SeqNum+1 else 1
PrevInDate: In_Date

In the output derivation of the Seq_Num put

Code: Select all

If Len(SeqNum) <3 then FMT(SeqNum,"3'0'R") else SeqNum

That should take care of it

[ak77]

Thanks,

You mean to say take the sorted input and just use one transformer where you check if the Prev_In_Date = Cur_In_Date.

If I just assign the field In_Date to a Stage Variable, does this Stage Variable hold the prev value

Am sorry I am not sure how to save the previous value


Thanks again
AK

[DSguru2B]

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.

[ak77]

Thanks,

So If I check for @INROWNUM = 1 does this reset for every group?
I thought @INROWNUM just gives the number of the row that is coming to the transformer

How does the seqnum reset for each of the group?

AK

[DSguru2B]

@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

Code: Select all

SeqNum:If In_Date = PrevInDate or @INROWNUM = 1 then SeqNum+1 else 1 
PrevInDate: In_Date 

when the condition is not satisfied, it specifies 1 to SeqNum which in other words is reseting the seq.

[ak77]

Yeah I was thinking abt this while in lunch
And figured out that I asked a stupid question

Anyways this should work I guess and thanks for answering patiently

I take this moment to thank everybody

Whenever I had an issue, I always got a solution from this forum

Thanks again,
AK

[DSguru2B]

Not a problem. Let us know if you achieved your purpose. Also let us know if you get stuck somewhere, we will be glad to help you out

[ak77]

Yes thanks

I used the stagevariable to store the previous value and incremented the SeqNum with the logic given by you
And it worked fine

Thanks again
AK