searching minimum and maximum for series of consecutive numbers

[addygoyal]

Hi Guys,
Need your help on this typical one

I ve got a string field {ticketNumber} but the values in this field are numbers i want to list the minimum and maximum of all the series made up of consecutive numbers within these values.

For example the value for the {ticketNumber} are
{ticketNumber}
1
2
3
4
13
14
15
16

I want results as
Minimum Maximum
1 4
13 16
Hopin you ppl turn out with something
Thanks
Addy

 

[Ngolem]

Is your string like this?

123413141516

What separates the numbers? How can I tell 13 from 1 3?

Or have you mis-described it and you have a series (not a string) of numbers

Jim Broadbent

 

[addygoyal]

No Jim my string is not like 123413141516

The string field has many number values and 1, 2, 3, 4, 13, 14, 15, 16 are all different values. So as you described it is a series of numbers.

And thanks for showing interest

Addy

 

[polash]

I think you know VB5/6. I solved your problem with VB. May there are some problem within this program. Because I solve it within a very short time.

At first Place a command button in a VB form and copy the following program into it's click event.
'''''''''''''''''''''''''''''''''''''''
Dim MinNum As Integer
Dim MaxNum As Integer
Dim x As Integer
x = InputBox("Enter number of Tickets&quot
ReDim a(0 To x) As Integer
For i = 0 To x - 1
a(i) = InputBox("Please enter your Ticket No(s)&quot
Print a(i)
Next
MinNum = a(0)
Print "Min No" & "---------" & "Max No"
For i = 1 To x
If a(i) = a(i - 1) + 1 Then
If MinNum = 0 Then MinNum = a(i - 1)
If MinNum > 0 Then MaxNum = a(i)
Else
If MinNum > 0 Then
Print MinNum & " ------" & MaxNum
End If
If i = x Then
If MaxNum <> a(x - 1) Then Print &quot;Remaining Number is-&quot; & a(x - 1)
End If
MinNum = 0
MaxNum = 0
End If

Next

 

[Ngolem]

Ok...now we are getting somewhere....so the value is a comma delimited string.


Before I propose a solution How do you want this string handled???

1,2,3,6,9

Must there be at least 2 consecutive numbers??
If so, do you want non consecutive numbers ignored, flagged or what?

I believe this can be done in one formula using arrays to store the results...

ARE THESE NUMBERS AS SIMPLE AS YOU DESCRIBE??? Just number

Jim Broadbent

 

[polash]

NGOLEM,

OK, to solve your problem if the number series is as like 1,2,3,6,7,8 then you should first seperate each value through different program for comma or other delimeted. We can do it easily.

You also said about non cosecutive numbers. I think it can be solved easily.
At the first of my solution I wrote I solve it with a very short time. But it's not a tuff thing for non consecutive numbers I think.

Polash
mamunal@yahoo.com

 

[Ngolem]

I HAVE the solution.....I am asking the person who posed the question HOW THEY WANTED THE SPECIAL CASES HANDLED

no...it is not hard.....but I would like to attack the whole problem....not a bit at a time

Also...I believe the &quot;ticket numbers&quot; as displayed are not real...they are an over simplification since they are to simple....I would like to have exact representation of the numbers to not have to do this twice

Jim Broadbent

 

[lbass]

Here's a solution, though not that simple.

First create a formula converting the ticket number to a value {@valtick}:

val({table.tickno})

Also create a second formula {@strtick} ensuring that the ticket number always has two digits:

totext({@valtick},&quot;00&quot

Next, if you have a group, let's say on {table.stores}, create a reset formula {@resetaccum} to be placed in the group header:

whileprintingrecords;
stringvar accum := &quot;&quot;;

Start by accumulating ticket numbers in {@accum} (to be placed in the details section):

whileprintingrecords;
stringvar accum;

if onfirstrecord or
{@valtick} = next({@valtick}) - 1 then
accum := accum + {@strtick} +&quot;,&quot; else
accum := &quot;&quot;;

Then create another formula {@result} also placed in the details section:

whileprintingrecords;
stringvar accum;
stringvar result;

if {table.store} = next({table.store}) and
{@valtick} < next({@valtick}) - 1 and
len(accum) > 0 then
result := left(accum,2)&&quot; &quot;&{@strtick} else

if {table.store} = next({table.store}) and
{@valtick} < next({@valtick}) -1 and
len(accum) = 0 then
result := {@strtick} &&quot; &quot;&{@strtick} else

if {table.store} <> next({table.store}) and
len(accum) > 0 then
result := left(accum,2) &&quot; &quot;&{@strtick} else

if {table.store} <> next({table.store}) and
len(accum) = 0 then
result := {@strtick} &&quot; &quot;&{@strtick};

The above assumes a two-digit ticket number, but you could adapt this to higher numbers by changing the format for {@strtick} and changing the numbers where the &quot;left&quot; function is used.

You can suppress {@accum} and then go to format section->details->suppress if blank to get rid of excess white space.

-LB

 

[addygoyal]

Jim
The case with
1,2,3,6,9 as values the results should be dispayed as

Minimum Maximum
1 3
6 6
9 9
and one more thing Jim 1,2,3,6,9 is not a string delimited by coma, they are different values for the field
such as
{Field value1} = 1
{Field value2} = 2
{Field value3} = 3
{Field value4} = 6
{Field value5} = 9
ie they are values for the same field in different rows of the database table.
I hope that will make it more clearer
and sorry for the delay

Addy

 

[addygoyal]

Hi

About the Ticket numbers
they are like
718458
718459
718460
718461
718462
718463
4453622
4453623
4453624
4453625
4662301
4662302
4662303
4662304
i hope now i ve cleared the picture a little bit more

Addy

 

[Ngolem]

yes...and as you can see by not laying this out like this you have resulted in 2 POSTED answers that cannot do the job

That is why I want details on something before I post a response.

You still haven't given all the details but close enough for now.

I will assume the ticket is a string so it needs to be converted to a number as LBass has said. I will also ASSUME because you did not answer my question that any odd tickets will be reported as both a minimum and maximum for the series and not ignored

I will also assume that there is some kind of grouping for this data...not the ticket field itself.

to do this I would have 2 arrays initialized as follows and placed in the Group header

//@initialize (suppressed)

WhilePrintingRecords;

if not InRepeatedGroupHeader then
(
//assignment 100% more min/max elements than you expect
//ie. if you expect 50 min/max's then initialize for 100
stringVar array MaxTick := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;];
stringVar array MinTick := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;];
numberVar Pointer := 0;
);


this array is initialized to null to make displaying easier later.

The details section is suppressed and the following calculation formula is placed inside the details.

//@Calc

whilePrintingRecords;
stringVar array MaxTick ;
stringVar array MinTick ;
numberVar Pointer ;

if onFirstRecord then
(
Pointer := Pointer + 1;
MinTick[Pointer] := {table.ticket}; //assuming this is a string
MaxTick[Pointer] := {table.ticket};
)
else if tonumber(previous({table.ticket})) = tonumber({table.ticket}) - 1 then
MaxTick[Pointer] := {table.ticket}
else
(
Pointer := Pointer + 1;
MinTick[Pointer] := {table.ticket};
MaxTick[Pointer] := {table.ticket};
);

now to display the results create display formulas in that Group footer

I am going to ASSUME that the ticket number will not exceed 10 digits. Since if you are using a version less than CR 9.0 you can have only 254 chars/formula....this means we can display about 20 min/max pairings (we add extra chars in creating the formula)

in the first subsection create the column headers for min/max and then place display formulas under them

create say for 100 possible pairings 5 formulas like the following

//@DisplayMin 1-20

whilePrintingRecords;
stringVar array MinTick ;
numberVar Pointer ;
StringVar Result := &quot;&quot;;

for Pointer := 1 to 20 do
(
Result := Result + MinTick[Pointer] + chr(13) + chr(10);
);

Result;

//@DisplayMax 1-20

whilePrintingRecords;
stringVar array MaxTick ;
numberVar Pointer ;
StringVar Result := &quot;&quot;;

for Pointer := 1 to 20 do
(
Result := Result + MaxTick[Pointer] + chr(13) + chr(10);
);

Result;

Place these under the first 2 columns and create the rest of the display formula 21-40, 41-60, .... the same way...you should be able to place formulas to handle 100 pairings in one section....make sure you enable the &quot;can grow&quot; for each formula.

If you have more that 100 potential...then just create another section of simialr formulas. The null ones won't display anything and you just enable the &quot;Suppress Blank Section&quot; to eliminate the blank section when not needed.

If you want the column headers (Minimum and Maximum) to disappear if there is no pairings then just add this formula to the conditional suppress of the column labels

eg. for the second set of pairings (21-40)...you can use the same formula for min and max column headers

WhilePrintingRecords;
StringVar array MaxTick;
MaxTick[21] = &quot;&quot;;

That should solve you report problem.

Jim Broadbent

 

[lbass]

Jim,

I guess you are counting my solution among those not working, but it worked correctly when I tested it, even with numbers like those posted--although granted, more initial info would have been useful.

-LB

 

[Ngolem]

sorry Lbass....you are correct...I didn't give your solution a thorough read....I just hate half questions requiring several rewrites

Your solution may work but I can see problems with the size of the numbers involved (254 char prob)when accumulating them....and I am not sure of your display in the details field....think it has to be done in the footer.

Overall I think mine is an easier approach to use.

Jim Broadbent