Checking for consequtive number within a group

padmathiagarajan

Forums

Hi,
I am trying to write a script to find out the consquetive invoices of a vendor from vendor ledger.
Input  (Consequtive check is a computed field)

Vendor number
Invoice no
Amt
Consequtive check

1
123
10
Start

1
124
20
Yes

1
125
30
Yes

1
301
400
No

1
302
200
End

2
2134
7000
Start

3
6162
100
Start

3
6163
100
Yes

3
6164
100
Yes

3
6165
400
Yes

3
6166
500
End

Output

Vendor
Purchase amt
No of invoices
No of consequtive invoices

1
660
5
2

2
7000
1
0

3
1200
5
4

How can i achieve this through a script. Could you pls help.
 
Regards
Padma

Brian Element Thu, 07/27/2017 - 07:38

Hi Padma,

In your above example I understand the results you want except for Vendor 3.  Should the number of consecutive invoices be 1 and not 4 as there is no break?  I am asking as for Vendor 1 there are two runs of consecutive invoices 123-125 and 301-302.

Let me know and I will see what I can do to help you out.

Brian

padmathiagarajan Fri, 07/28/2017 - 01:39

Basically i want to have the count of consequtive invoices. Not the series. So may be for vendor 1 it should be 4 and not 2. 
 
Regards
Padma T

Brian Element Fri, 07/28/2017 - 11:56

Hi Padma,

Try this script out.  To use it the vendor fields must be a character field and the invoice field must be numeric.  I am getting the expected results from your data but it will be good to test it on a longer file.  Let me know if you have any problems or it doesn't do what it is supposed to do.

Thanks

Brian

padmathiagarajan Sat, 07/29/2017 - 01:07

Hi Brian.
Thanks for your efforts.
I am getting error on line 77- Overflow.
I kept vendor code as character and invoice field numeric.
Regards
Padma T

Brian Element Sat, 07/29/2017 - 05:26

Hi Padma,

That line is totally the amounts.  Was it able to get through part of the analysis. You should see a new file called Consequtive Numbers, you migth have to close it and open it again to see the contents.  Did it get through any of the vendors as there should be a line per vendor.  If it got through some do the amounts look right for the different vendors?  Usually an Overflow error means that the variable is not large enough to hold the item so in this case the next vendor are the amounts really large?

I am just trying to figure out what might be wrong.  Any chance you could share the file with me so I could run it?

Thanks

Brian

mcen.stellar Thu, 01/23/2020 - 12:50

Hi Brian,  i am looking for a script similar to what the original post wanted to do.  I tried clicking the file you attached but the text in the file is all lumped together... any better way for me to see get it better formatted for use? 

Brian Element Thu, 01/23/2020 - 13:19

Try downloading the script file and loading it in the IDEAScript editor and see if that works for you.  Or try opening it in a text editor such as Notepad ++

The website encountered an unexpected error. Try again later.