VFP9 Fill Command? 1

[nicktagz]

I'm looking for a way to fill in data from one row to another, much like the fill up or fill down function in excel. Basically what I want to do is if the row is blank fill it in with the data below it. Please see the example below.

Beginning Result:

1 |
2 | 1
3 | 2
4 |
5 | 3

End Result:

1 | 1
2 | 1
3 | 2
4 | 3
5 | 3

 

[Mike Lewis]

How is the data stored? In an array? Or a table (DBF or cursor)?

I'll assume it's in an array. Also, it's not clear what the function of the first column is. I'll assume it's just a sequence of consecutive integers that have no bearing on the result.

On that basis, something like this should work:

* Assume data is in a 2-column array named laData
lnRows = ALEN(laData, 1)	&& number of rows
FOR lnI = lnRows TO 2 STEP -1
  * loop backward through the array
  IF EMPTY(laData(lnI - 1, 2))
    laData(lnI - 1, 2) = laData(lnI, 2)
  ENDIF 
ENDFOR

This is just off the top of my head, and not tested, but it should give you the general idea.

If my assumptions are wrong, please clarify the requirements.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[nicktagz]

Apologizes, it's a DBF table. The first column is a name and the second column is an account number.
Col.1 | Col.2
a |
a | 1
a | 1
b |
b | 2
b | 2
c |
c | 3
c | 3

I'm just looking to fill in that first blank row with the data below it.

 

[Mike Lewis]

No problem. The principle is the same. You start at the bottom, and step backwards, filling in the data as you go. Something like this:

* Assume the DBF is named MyTable and the second column
* is named Field2
USE MyTable
GO BOTTOM 
DO WHILE .T.
  lnNumber = Field2
  SKIP -1
  IF BOF()
    EXIT
  ENDIF 
  IF EMPTY(Field2)
    REPLACE Field2 WITH lnNumber
   ENDIF 
ENDDO

Again, I have not tried to test this, but I think it should work.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[nicktagz]

Thank you very much, this is exactly what I was looking for.

 

[Mike Lewis]

Glad to have been of help. And, by the way, welcome to the forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

As with many things in VFP there are a variety of ways to accomplish anything.

While not trying to take anything away from Mike's reply, here's another approach:

* Assuming Table is named MyTable and the fields are:  Col1 | Col2
* Other assumptions:
*    All groups of Col1's have only a single non-blank Col2 value
*    No group of Col1's have NO Col2 values
*
USE MyTable IN 0
* --- Get Reference Cursor with Col1 & Col2 values ---
SELECT DISTINCT *;
   FROM MyTable;
   WHERE !EMPTY(Col1);
   AND !EMPTY(Col2);
   INTO CURSOR Col1Col2 READWRITE
SELECT Col1Col2
INDEX ON Col1 TAG Col1

* --- Establish Relation Between MyTable and Reference Cursor ---
SELECT MyTable
SET RELATION TO Col1 INTO Col1Col2
* --- Populate Everything in MyTable with Col2 values from Reference Cursor ---
REPLACE ALL Col2 WITH Col1Col2.Col2 FOR !EMPTY(Col1Col2.Col1)
<now do whatever>

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

There was quite a similar discussion here already: thread184-1767403

While your problem seems solved, you might find the one or other hint on being cautious about what to do and how, to not fill in gaps wrongly.

I can say Mikes code is fine under the prerequisite the last row of each group has the value you want to fill up from there. But it's a requirement, for which you would need to look at all data, quite impossible to do, because while you're at that, you could also manually fill in the gaps. So you also would need to get back the unfilled data and test your assumptions.

Bye, Olaf.