I want to add SeqNo for each and every invoice based on invoice no and record type 605..There are multiple 605 records in each invoice,i want to assign seqNo for each Inv+605 combination and the output should be like below..
That just looks like a job for Stage Variables and 'group change detection' to me. Check for 'x 605' and for every time you see a new value of 'x' set a sequence number stage variable's value to 1. As long as 'x' doesn't change, increment the sequence number for each occurance of '605'.
This will work fine as long as your incoming data is properly sorted, which it seems to be from your example.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Sort the data on the field "RecType". in transformer stage take 3 stage variables.
1) CurrentSv -> Input field "RecType"
2) CounterSv -> If CurrentSv = PreviousSv Then CounterSv Else CounterSv +1
3) PreviousSv -> CurrentSv.
You will have to initialize the CounterSv value to 0. i.e the initial value will be 0. Populate the output "SeqNo" field with CounterSv.
Maveric wrote:Sort the data on the field "RecType". in transformer stage take 3 stage variables.
1) CurrentSv -> Input field "RecType"
2) CounterSv -> If CurrentSv = PreviousSv Then CounterSv Else CounterSv +1
3) PreviousSv -> CurrentSv.
You will have to initialize the CounterSv value to 0. i.e the initial value will be 0. Populate the output "SeqNo" field with CounterSv.
You need to adapt what Maveric posted with the details in my post. He has missed the point that the sequence number needs to reset back to 1 when the "Inv.No" changes... the 'x' mentioned in my post.
And the first stage variable isn't really needed.
-craig
"You can never have too many knives" -- Logan Nine Fingers
chulett wrote:You need to adapt what Maveric posted with the details in my post. He has missed the point that the sequence number needs to reset back to 1 when the "Inv.No" changes... the 'x' mentioned in my post.
And the first stage variable isn't really needed.
could you please give that code..if you don't mind please
Sort the data on both the input fields. in transformer stage take 3 stage variables.
1) CurrentSv -> Input field Inv.No : RecType
2) CounterSv -> If CurrentSv = PreviousSv Then CounterSv Else CounterSv +1
3) PreviousSv -> CurrentSv.
Guess this derivation should do. If this doesn't work can u explain the requirement clearly with say 5 or 6 records as example.
Chulette,
I tried your code but its giving svSeqNo 1 for all the records..I tried to change several times and it didn't work for me..
Thanks for your time and would appreciate if you recommend any suggestions..
edward_m wrote:I tried your code but its giving svSeqNo 1 for all the records.
I honestly don't see how that is possible. Verify some things for me.
1) You created the stage variables in the order shown
2) You created the derivations as shown, adjusted for your actual names
3) You set the Initial Values on the Properties tab as shown
4) Your data is sorted correctly by InvNo and RecType
5) Neither field (InvNo or RecType) can ever be null in your file
Changes shouldn't have been needed other than to use your real link and field names, unless I've missed something here...
-craig
"You can never have too many knives" -- Logan Nine Fingers
I did double check my code and its exactly in order,variable initialised as you mentioned..
Actually i copied your code from this post and changed link names only..
Ok, I see the issue - your data is not sorted in the order I was expecting and as noted in my point #4. You can get 605 record types anywhere in the invoice. Try this instead: