To Assign Seq No.s to Invoice

edward_m · Post by **edward_m** » Wed Sep 12, 2007 7:23 am

I am stuck in the middle of assigning sequence numbers to particular invoices based on invoice no and record type 605 combination..

My input is..

Inv.No RecType
1 605
1 610
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 605
1 610
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 611
1 630
2 605
2 610
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 605
2 610
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 611
2 630
3 605
3 610
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 605
3 610
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 611
3 630

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..

InvNo RecType SeqNo
1 605 1
1 610 1
1 611 1
1 611 1
1 611 1
1 611 1
1 611 1
1 611 1
1 611 1
1 611 1
1 611 1
1 605 2
1 610 2
1 611 2
1 611 2
1 611 2
1 611 2
1 611 2
1 611 2
1 611 2
1 611 2
1 611 2
1 630 2
2 605 1
2 610 1
2 611 1
2 611 1
2 611 1
2 611 1
2 611 1
2 611 1
2 611 1
2 611 1
2 611 1
2 605 2
2 610 2
2 611 2
2 611 2
2 611 2
2 611 2
2 611 2
2 611 2
2 611 2
2 611 2
2 611 2
2 630 2
3 605 1
3 610 1
3 611 1
3 611 1
3 611 1
3 611 1
3 611 1
3 611 1
3 611 1
3 611 1
3 611 1
3 605 2
3 610 2
3 611 2
3 611 2
3 611 2
3 611 2
3 611 2
3 611 2
3 611 2
3 611 2
3 611 2
3 630

Please help me out ..how to get this to be done.
Thanks in Advance

chulett · Post by **chulett** » Wed Sep 12, 2007 7:29 am

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.

Maveric · Post by **Maveric** » Wed Sep 12, 2007 7:36 am

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.

edward_m · Post by **edward_m** » Wed Sep 12, 2007 7:53 am

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.

Maverick..
Your code didn't work for me..
Thanks.

Maveric · Post by **Maveric** » Wed Sep 12, 2007 8:02 am

Which part dint work? what output are you getting? Is it populating the output field? Check the stage variable data types.

chulett · Post by **chulett** » Wed Sep 12, 2007 8:10 am

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.

edward_m · Post by **edward_m** » Wed Sep 12, 2007 8:19 am

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

Maveric · Post by **Maveric** » Wed Sep 12, 2007 8:40 am

Oops. dint see it.

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.

Kirtikumar · Post by **Kirtikumar** » Wed Sep 12, 2007 10:33 am

Sort your input on the input cols.

Define stage vars and derivations as follows:

Code: Select all

PrevRecType_SVAR => CurrRecType_SVAR
CurrRecType_SVAR => Incoming link column RecType
SeqNo_SVAR =>
	If PrevRecType_SVAR = CurrRecType_SVAR
	Then
		If SeqNo_SVAR = 2
		Then
			1
		Else
			2
	Else
		SeqNo_SVAR

Assign the SeqNo_SVAR to output. This should provide you the required output.

[edited to add] Please note the order of stage vars is imp when you code this.

chulett · Post by **chulett** » Wed Sep 12, 2007 12:46 pm

There's no upper limit of '2' to this sequence that Edward M has mentioned.

Code: Select all

svTargetType  ->  Link.RecType = 605
svCurrKey     ->  Link.InvNo : '|' : Link.RecType
svNewKey      ->  svCurrKey <> svPrevKey
svSeqNo       ->  If svTargetType Then If svNewKey Then 1 Else svSeqNo + 1 Else svSeqNo
svPrevKey     ->  svCurrKey

Off the top of my head, what I would do. Set the Initial Values as follows:

Code: Select all

svTargetType   @FALSE
svCurrKey      @NULL
svNewKey       @FALSE
svSeqNo        0
svPrevKey      '?'

That should get you pretty darn close, I believe, that and be easy to understand what's going on.

edward_m · Post by **edward_m** » Wed Sep 12, 2007 3:37 pm

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 · Post by **edward_m** » Wed Sep 12, 2007 3:39 pm

DSGuru2..
You helped me before with similar code..
Can you please throw some light on this

chulett · Post by **chulett** » Wed Sep 12, 2007 5:22 pm

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...

edward_m · Post by **edward_m** » Wed Sep 12, 2007 8:02 pm

Chulette,

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..

chulett · Post by **chulett** » Wed Sep 12, 2007 8:42 pm

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:

Code: Select all

svTargetType  ->  Link.RecType = 605
svCurrKey     ->  Link.InvNo
svNewKey      ->  svTargetType And svCurrKey <> svPrevKey
svSeqNo       ->  If svTargetType Then If svNewKey Then 1 Else svSeqNo + 1 Else svSeqNo
svPrevKey     ->  If svTargetType Then svCurrKey Else svPrevKey

Same Initial Values:

Code: Select all

svTargetType   @FALSE
svCurrKey      @NULL
svNewKey       @FALSE
svSeqNo        0
svPrevKey      '?'

Still winging it without any test data or a job to play with. Hopefully more better this time.