Splitting a year range like 92-95 to show 92 93 94 95 1

[jeffm777]

I have a field in a database that currently shows, for example, the following:

92-95 AEROSTAR (FORD VAN)

or

99 BUICK LESABRE

If the field contains like 92-95, I need a formula to convert the years to show 92 93 94 95 and extract them so only the years show without the make/model info.

 

[jeffm777]

If the field doesn't show a range, I just need the formula to show the two numbers like 99.

 

[briangriffin]

This will work unless:

1. You have any four digit years.
2. The car description contains numbers, such as Mustang 2.

It doesn't seem like either would be the case, so:

// First extract only the numbers.

whileprintingrecords;
stringvar v_newcar := "";
numbervar v_counter := 1;

while v_counter <= len({databasefield})
do

(if isnumeric({databasefield}[v_counter])
then
v_newcar := v_newcar + {databasefield}[v_counter]
else v_newcar;
v_counter := v_counter + 1);

//////////////////////////////////////////////

// Next create an array and split the numbers into two digit values.

stringvar array v_array := [left(v_newcar,2),""];
numbervar v_counter2 := 3;


while v_counter2 <= len(v_newcar)
do

(v_array[v_counter2 - 1] := mid(v_newcar,v_counter2,v_counter2 + 1);
redim preserve v_array[ubound(v_array) + 1];
v_counter2 := v_counter2 + 2);

/////////////////////////////////////////////////

// Last create another array and populate it with the min, max, and all values in between.

numbervar v_min := tonumber(v_array[1]);
numbervar v_max := tonumber(v_array[ubound(v_array) - 1]);
stringvar array v_display := [totext(v_min,0)];
numbervar v_counter3 := 2;
numbervar v_counter4 := v_min;


while v_counter4 <= v_max
do

(redim preserve v_display[v_counter3];
v_display[v_counter3] := totext(v_counter4 + 1,0);
v_counter3 := v_counter3 + 1;
v_counter4 := v_counter4 + 1);

//

if ubound(v_display) = 2 then v_display[1]
else
join(v_display," ");

 

[briangriffin]

Whoops! In the last section, change:

while v_counter4 <= v_max

to

while v_counter4 < v_max

 

[jeffm777]

Thanks Brian. WOW that is a lot of code. I am getting an error and that's my fault for not providing enough examples of the field.

I'm getting "A subscript must be between 1 and the size of the array" at...

(v_array[v_counter2 - 1] := mid(v_newcar,v_counter2,v_counter2 + 1);

when it encountered...

98 AUDI A4; 99 AUDI A4; 98 VOLKSWAGEN PASSAT; 99 VOLSKWAGEN PASSAT

I don't care about the make and model or any of the other years listed...just need the first "98" to be extracted.

Thanks.

 

[briangriffin]

So if the record starts with 92-95 you want:

92 93 94 95

but for:
98 AUDI A4; 99 AUDI A4; 98 VOLKSWAGEN PASSAT; 99 VOLSKWAGEN PASSAT

you just want 98? Not 98 99 ?

Are there any other record formats that we need to consider? Do any records start with 92 - 95, rather than 92-95?

 

[jeffm777]

Correct. If the record starts 92-95, I want 92 93 94 95 and if the record starts 98, I just want 98. If there is any easy way to analyze the field to look for other years listed for the same manufacturer and model, then yes, I would like it to show 98 99 but it's not necessary.

I don't see any other record formats that need to be considered.

 

[jeffm777]

Oh and I do see some models that have numbers like a RAV-4 and a VOLVO 70 SERIES.

 

[briangriffin]

Replace the first block of code with:

whileprintingrecords;
stringvar v_newcar := "";
numbervar v_counter := 1;

while isnumeric({Sheet1_.Values}[v_counter]) or {Sheet1_.Values}[v_counter] = "-"
do

(if isnumeric({Sheet1_.Values}[v_counter])
then
v_newcar := v_newcar + {Sheet1_.Values}[v_counter]
else v_newcar;
v_counter := v_counter + 1);

That will return the first year. It would be tough to extract the other years, since we can't really determine if the number is a year or part of the model.

This would probably be better done at the database level by creating a stored procedure that splits the records at the semicolon, strips out the model text, reassembles the records, and delivers the preformatted records to the report.

 

[jeffm777]

Ok. That works but it's dropping off the first 0 in the years. For example, if the years are 02-06, it's showing them as 2 3 4 5 6 instead of 02 03 04 05 06 and if it's just a single year, 04 is showing 4.

 

[jeffm777]

Of course I didn't have any vehicles after the year 2000 listed in any of my examples. Sorry bout' that.

 

[briangriffin]

Replace the last block with:

// Last create another array and populate it with the min, max, and all values in between.

numbervar v_min := tonumber(v_array[1]);
numbervar v_max := tonumber(v_array[ubound(v_array) - 1]);
stringvar array v_display := [totext(v_min,0)];
numbervar v_counter3 := 2;
numbervar v_counter4 := v_min;


while v_counter4 < v_max
do

(redim preserve v_display[v_counter3];
v_display[v_counter3] := totext(v_counter4 + 1,0);
v_counter3 := v_counter3 + 1;
v_counter4 := v_counter4 + 1);


numbervar v_loop :=1;
while v_loop <= ubound(v_display)
do

(if len(v_display[v_loop]) = 1 then 
v_display[v_loop] := "0" + v_display[v_loop];
v_loop := v_loop + 1);

if ubound(v_display) = 2 then v_display[1]
else
join(v_display," ");

 

[jeffm777]

You my friend are a genius! Thanks for your diligence. Works like a charm.

 

[briangriffin]

Won't work if the range crosses 2000, though - for instance

98-02 Ford Mustang

If that's a possibility then we can trap for that - but not today

 

[jeffm777]

Yeah. Crossing decades seems to be an issue. Crossing from 89 to 90 shows 9 90 91 92 93 and crossing 99 to 00 drops off any year after 99. No worries though, just when ever you have time to dive into it again will be fine.

Thanks!!!

 

[jeffm777]

Correction, if the field crosses 2000, it only shows the first year in the range. For example, 98-03 only shows 98.

 

[briangriffin]

Here's the whole new, improved version:

// First extract only the numbers.

whileprintingrecords;
stringvar v_newcar := "";
numbervar v_counter := 1;

while isnumeric({Sheet1_.Values}[v_counter]) or {Sheet1_.Values}[v_counter] = "-"
do

(if isnumeric({Sheet1_.Values}[v_counter])
then
v_newcar := v_newcar + {Sheet1_.Values}[v_counter]
else v_newcar;
v_counter := v_counter + 1);

//////////////////////////////////////////////

// Next create an array and split the numbers into two digit values.

stringvar array v_array := [left(v_newcar,2),""];
numbervar v_counter2 := 3;


while v_counter2 <= len(v_newcar)
do

(v_array[v_counter2 - 1] := mid(v_newcar,v_counter2,v_counter2 + 1);
redim preserve v_array[ubound(v_array) + 1];
v_counter2 := v_counter2 + 2);

/////////////////////////////////////////////////
// Convert the years to four digits.

numbervar v_anothercounter := 1;
while v_anothercounter < ubound(v_array)
do

(if left(v_array[v_anothercounter],1) = "0" then v_array[v_anothercounter] := "20" + v_array[v_anothercounter]
else v_array[v_anothercounter] := "19" + v_array[v_anothercounter];
v_anothercounter := v_anothercounter + 1);


/////////////////////////////////////////////////
// Last create another array and populate it with the min, max, and all values in between.

numbervar v_min := tonumber(v_array[1]);
numbervar v_max := tonumber(v_array[ubound(v_array) - 1]);
stringvar array v_display := [totext(v_min,0)];
numbervar v_counter3 := 2;
numbervar v_counter4 := v_min;


while v_counter4 < v_max
do

(redim preserve v_display[v_counter3];
v_display[v_counter3] := totext(v_counter4 + 1,0);
v_counter3 := v_counter3 + 1;
v_counter4 := v_counter4 + 1);


numbervar v_loop :=1;
while v_loop <= ubound(v_display)
do

(v_display[v_loop] := right(v_display[v_loop],2);
v_loop := v_loop + 1);

if ubound(v_display) = 2 then v_display[1]
else
join(v_display," ");

 

[jeffm777]

Cool. That fixed the crossing year 2000 problem but I'm still missing the "8" from "89 90 91 92 93 94 95 96 97 98" so it's showing "9 90 91 92 93 94 95 96 97 98".

 

[briangriffin]

Not sure what's happening at your end - those records are working for me. I've tried corrupting the records by adding spaces, etc. but I can't recreate the display values that you're getting. Here's a modified version that strips out the spaces, just in case:

// First extract only the numbers.

whileprintingrecords;
stringvar v_newcar := "";
numbervar v_counter := 1;
stringvar v_nospaces := replace({Sheet1_.Values}," ","");

while isnumeric(v_nospaces[v_counter]) or v_nospaces[v_counter] = "-"
do

(if isnumeric(v_nospaces[v_counter])
then
v_newcar := v_newcar + v_nospaces[v_counter]
else v_newcar;
v_counter := v_counter + 1);

//////////////////////////////////////////////

// Next create an array and split the numbers into two digit values.

stringvar array v_array := [left(v_newcar,2),""];
numbervar v_counter2 := 3;


while v_counter2 <= len(v_newcar)
do

(v_array[v_counter2 - 1] := mid(v_newcar,v_counter2,v_counter2 + 1);
redim preserve v_array[ubound(v_array) + 1];
v_counter2 := v_counter2 + 2);

/////////////////////////////////////////////////
// Convert the years to four digits.

numbervar v_anothercounter := 1;
while v_anothercounter < ubound(v_array)
do

(if left(v_array[v_anothercounter],1) = "0" then v_array[v_anothercounter] := "20" + v_array[v_anothercounter]
else v_array[v_anothercounter] := "19" + v_array[v_anothercounter];
v_anothercounter := v_anothercounter + 1);


/////////////////////////////////////////////////
// Last create another array and populate it with the min, max, and all values in between.

numbervar v_min := tonumber(v_array[1]);
numbervar v_max := tonumber(v_array[ubound(v_array) - 1]);
stringvar array v_display := [totext(v_min,0)];
numbervar v_counter3 := 2;
numbervar v_counter4 := v_min;


while v_counter4 < v_max
do

(redim preserve v_display[v_counter3];
v_display[v_counter3] := totext(v_counter4 + 1,0);
v_counter3 := v_counter3 + 1;
v_counter4 := v_counter4 + 1);


numbervar v_loop :=1;
while v_loop <= ubound(v_display)
do

(v_display[v_loop] := right(v_display[v_loop],2);
v_loop := v_loop + 1);

if ubound(v_display) = 2 then v_display[1]
else
join(v_display," ");

 

[jeffm777]

Not sure what the issue is either but still having the same problem.

"89-98 MAZDA VAN" looks like this "9 90 91 92 93 94 95 96 97 98