filtering data from a dbf file

Hi,

Suppose I have the test.dbf file as given below. Group is of the form
Iteration x | y where x is the iteration number, and y is the some product
code number. Within each iteration I would like to sum the values at a
specific time for all the product numbers in that iteration, so my two (for
each iteration) output files look like given below. I hope this is clear. My
question: what code should I use. I am a total newby so any examples/samples
are welcome!

Cheers,
John.

test.dbf

product group time val
test Iteration 1 | 1 0 0.5
test Iteration 1 | 1 1 0.6
test Iteration 1 | 1 2 0.7
test Iteration 1 | 1 3 0.7
test Iteration 1 | 2 0 0.4
test Iteration 1 | 2 1 0.3
test Iteration 1 | 2 2 0.3
test Iteration 1 | 2 3 0.4
test Iteration 1 | 3 0 0.5
test Iteration 1 | 3 1 1.1
test Iteration 1 | 3 2 1.2
test Iteration 1 | 3 3 0.3
test Iteration 1 | 4 0 0.7
test Iteration 1 | 4 1 0.4
test Iteration 1 | 4 2 0.5
test Iteration 1 | 4 3 1.1
test Iteration 2 | 1 0 0.3
test Iteration 2 | 1 1 0.7
test Iteration 2 | 1 2 0.4
test Iteration 2 | 1 3 0.7
test Iteration 2 | 2 0 0.4
test Iteration 2 | 2 1 0.5
test Iteration 2 | 2 2 0.7
test Iteration 2 | 2 3 0.3
test Iteration 2 | 3 0 0.6
test Iteration 2 | 3 1 0.7
test Iteration 2 | 3 2 0.4
test Iteration 2 | 3 3 0.7
test Iteration 2 | 4 0 0.4
test Iteration 2 | 4 1 0.5
test Iteration 2 | 4 2 0.7
test Iteration 2 | 4 3 0.3

test1.dbf

product group time val
test Iteration 1 0 2.1
test Iteration 1 1 2.4
test Iteration 1 2 2.7
test Iteration 1 3 2.5

test2.dbf

product group time val
test Iteration 2 0 1.7
test Iteration 2 1 3.4
test Iteration 2 2 2.2
test Iteration 2 3 2

John
Wed Apr 09 11:58:04 CDT 2008

By the way,

I'm using Visual Foxpro 9.0.

Thanks,

John.

Anders
Wed Apr 09 13:33:17 CDT 2008

Hi John
What is the Primary Key of the table?
If this line is the output of a summing
test Iteration 1 0 2.1
I can't figure out what rows in the data sum up to 2.1 and belong to product
code 0.
It would be nice if your could post a CREATE CURSOR and a few INSERT INTO
to show what's what.
That's language us database nerds understand.

-Anders

"John" <John@discussions.microsoft.com> wrote in message
news:5154AF12-273B-47F4-9902-7AF33C3BDCC0@microsoft.com...
> Hi,
>
> Suppose I have the test.dbf file as given below. Group is of the form
> Iteration x | y where x is the iteration number, and y is the some product
> code number. Within each iteration I would like to sum the values at a
> specific time for all the product numbers in that iteration, so my two
> (for
> each iteration) output files look like given below. I hope this is clear.
> My
> question: what code should I use. I am a total newby so any
> examples/samples
> are welcome!
>
> Cheers,
> John.
>
>
> test.dbf
>
> product group time val
> test Iteration 1 | 1 0 0.5
> test Iteration 1 | 1 1 0.6
> test Iteration 1 | 1 2 0.7
> test Iteration 1 | 1 3 0.7
> test Iteration 1 | 2 0 0.4
> test Iteration 1 | 2 1 0.3
> test Iteration 1 | 2 2 0.3
> test Iteration 1 | 2 3 0.4
> test Iteration 1 | 3 0 0.5
> test Iteration 1 | 3 1 1.1
> test Iteration 1 | 3 2 1.2
> test Iteration 1 | 3 3 0.3
> test Iteration 1 | 4 0 0.7
> test Iteration 1 | 4 1 0.4
> test Iteration 1 | 4 2 0.5
> test Iteration 1 | 4 3 1.1
> test Iteration 2 | 1 0 0.3
> test Iteration 2 | 1 1 0.7
> test Iteration 2 | 1 2 0.4
> test Iteration 2 | 1 3 0.7
> test Iteration 2 | 2 0 0.4
> test Iteration 2 | 2 1 0.5
> test Iteration 2 | 2 2 0.7
> test Iteration 2 | 2 3 0.3
> test Iteration 2 | 3 0 0.6
> test Iteration 2 | 3 1 0.7
> test Iteration 2 | 3 2 0.4
> test Iteration 2 | 3 3 0.7
> test Iteration 2 | 4 0 0.4
> test Iteration 2 | 4 1 0.5
> test Iteration 2 | 4 2 0.7
> test Iteration 2 | 4 3 0.3
>
>
> test1.dbf
>
> product group time val
> test Iteration 1 0 2.1
> test Iteration 1 1 2.4
> test Iteration 1 2 2.7
> test Iteration 1 3 2.5
>
>
> test2.dbf
>
> product group time val
> test Iteration 2 0 1.7
> test Iteration 2 1 3.4
> test Iteration 2 2 2.2
> test Iteration 2 3 2
>
>
>
>

John
Wed Apr 09 15:17:00 CDT 2008

Hi Anders,

Thanks for your reply. I am a real newbie to this to be honest. I have no
idea what the primary key is of this table. How do I find out. The example
below isn't to clear since the spacing disappered. In my first post it is
still clear.

Product: character named 'test'. This column not of importance!

Iteration: character of the form 'Iteration x | y'
where x is iteration number, y some product ID.

time: integer

So in test1.dbf the line test Iteration 1 0 2.1 is the sum over the 4
product ID's at time 0:

value at time 0 for: 'Iteration 1 | 1' = 0.5
value at time 0 for: 'Iteration 1 | 2' = 0.4
value at time 0 for: 'Iteration 1 | 3' = 0.5
value at time 0 for: 'Iteration 1 | 4' = 0.7

value at time 0 for Iteration 1 = 0.5 + 0.4 + 0.5 + 0.7 = 2.1

Is this more clear now? I could send you the dbf if you would like to.

Hope you can help a newbie.

John.

"Anders Altberg" wrote:

> Hi John
> What is the Primary Key of the table?
> If this line is the output of a summing
> test Iteration 1 0 2.1
> I can't figure out what rows in the data sum up to 2.1 and belong to product
> code 0.
> It would be nice if your could post a CREATE CURSOR and a few INSERT INTO
> to show what's what.
> That's language us database nerds understand.
>
> -Anders
>
> "John" <John@discussions.microsoft.com> wrote in message
> news:5154AF12-273B-47F4-9902-7AF33C3BDCC0@microsoft.com...
> > Hi,
> >
> > Suppose I have the test.dbf file as given below. Group is of the form
> > Iteration x | y where x is the iteration number, and y is the some product
> > code number. Within each iteration I would like to sum the values at a
> > specific time for all the product numbers in that iteration, so my two
> > (for
> > each iteration) output files look like given below. I hope this is clear.
> > My
> > question: what code should I use. I am a total newby so any
> > examples/samples
> > are welcome!
> >
> > Cheers,
> > John.
> >
> >
> > test.dbf
> >
> > product group time val
> > test Iteration 1 | 1 0 0.5
> > test Iteration 1 | 1 1 0.6
> > test Iteration 1 | 1 2 0.7
> > test Iteration 1 | 1 3 0.7
> > test Iteration 1 | 2 0 0.4
> > test Iteration 1 | 2 1 0.3
> > test Iteration 1 | 2 2 0.3
> > test Iteration 1 | 2 3 0.4
> > test Iteration 1 | 3 0 0.5
> > test Iteration 1 | 3 1 1.1
> > test Iteration 1 | 3 2 1.2
> > test Iteration 1 | 3 3 0.3
> > test Iteration 1 | 4 0 0.7
> > test Iteration 1 | 4 1 0.4
> > test Iteration 1 | 4 2 0.5
> > test Iteration 1 | 4 3 1.1
> > test Iteration 2 | 1 0 0.3
> > test Iteration 2 | 1 1 0.7
> > test Iteration 2 | 1 2 0.4
> > test Iteration 2 | 1 3 0.7
> > test Iteration 2 | 2 0 0.4
> > test Iteration 2 | 2 1 0.5
> > test Iteration 2 | 2 2 0.7
> > test Iteration 2 | 2 3 0.3
> > test Iteration 2 | 3 0 0.6
> > test Iteration 2 | 3 1 0.7
> > test Iteration 2 | 3 2 0.4
> > test Iteration 2 | 3 3 0.7
> > test Iteration 2 | 4 0 0.4
> > test Iteration 2 | 4 1 0.5
> > test Iteration 2 | 4 2 0.7
> > test Iteration 2 | 4 3 0.3
> >
> >
> > test1.dbf
> >
> > product group time val
> > test Iteration 1 0 2.1
> > test Iteration 1 1 2.4
> > test Iteration 1 2 2.7
> > test Iteration 1 3 2.5
> >
> >
> > test2.dbf
> >
> > product group time val
> > test Iteration 2 0 1.7
> > test Iteration 2 1 3.4
> > test Iteration 2 2 2.2
> > test Iteration 2 3 2
> >
> >
> >
> >
>
>
>

Anders
Wed Apr 09 19:05:03 CDT 2008

This is a multi-part message in MIME format.

------=_NextPart_000_0073_01C89AAF.4A3D6130
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi John=20
I suggested you do a ou=C3=B6d contain 'atomi
CREATE CURSOR Test ( iter C(15), time Int, valx Numeric( 4,1)
INSERT INTO Test VALUES ( 'Iteration 1 | 1', 0, 0.5)=20
INSERT INTO Test VALUES ( 'Iteration 1 | 1', 1, 0.6)=20
and so on.
There's a problem with 'Iteration 1 | 1' because it's really several =
different data items. One rule for setting up relational tables is that =
the column should contain 'atomic' item of data.=20
Now, in order to get that first 1 in 'Iteration 1 | 1' you have to use =
SUBSTR() to dig it out. It should be in a column by itself.=20
What is stuff like '1 | 1' and '1 | 3' supposed to mean and be used for? =
It's very awkward to handle if it's all in a single column. =20

***********
TEXT TO lcText noshow
Iteration 1 | 1, 0, 0.5
Iteration 1 | 1, 1, 0.6
Iteration 1 | 1, 2, 0.7
Iteration 1 | 1, 3, 0.7
Iteration 1 | 2, 0, 0.4
Iteration 1 | 2, 1, 0.3
Iteration 1 | 2, 2, 0.3
Iteration 1 | 2, 3, 0.4
Iteration 1 | 3, 0, 0.5
Iteration 1 | 3, 1, 1.1
Iteration 1 | 3, 2, 1.2
Iteration 1 | 3, 3, 0.3
Iteration 1 | 4, 0, 0.7
Iteration 1 | 4, 1, 0.4
Iteration 1 | 4, 2, 0.5
Iteration 1 | 4, 3, 1.1
Iteration 2 | 1, 0, 0.3
Iteration 2 | 1, 1, 0.7
Iteration 2 | 1, 2, 0.4
Iteration 2 | 1, 3, 0.7
Iteration 2 | 2, 0, 0.4
Iteration 2 | 2, 1, 0.5
Iteration 2 | 2, 2, 0.7
Iteration 2 | 2, 3, 0.3
Iteration 2 | 3, 0, 0.6
Iteration 2 | 3, 1, 0.7
Iteration 2 | 3, 2, 0.4
Iteration 2 | 3, 3, 0.7
Iteration 2 | 4, 0, 0.4
Iteration 2 | 4, 1, 0.5
Iteration 2 | 4, 2, 0.7
Iteration 2 | 4, 3, 0.3
endtext=20
ALINES(aArr,lcText,1+4,',',CHR(13)+CHR(10))
DIMENSION aArr(ALEN(aArr,1)/3,3)=20
CREATE CURSOR Test (iter Char(15), time int, valx Num(4,1))
INSERT INTO Test FROM ARRAY aArr=20
GO TOP IN Test
BROWSE LAST NOWAIT=20

SELECT SUBSTR(iter,11,1) As iter, time , SUM(valx) FROM Test ;
GROUP BY 1, time INTO CURSOR test1=20

Result:=20
Iter Time Sum_valx =20
1 0 2.1=20
1 1 2.4=20
1 2 2.7=20
1 3 2.5=20
2 0 1.7=20
2 1 2.4=20
2 2 2.2=20
2 3 2.0=20

-Anders=20

------=_NextPart_000_0073_01C89AAF.4A3D6130
Content-Type: text/html;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.6000.16608" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D"">
<DIV><FONT face=3DArial size=3D2>Hi John <BR>I suggested you do a =
ou=C3=B6d contain=20
'atomi<BR>CREATE CURSOR Test ( iter C(15), time Int, valx Numeric(=20
4,1)<BR>INSERT INTO Test VALUES ( 'Iteration 1 | 1', 0, 0.5) <BR>INSERT =
INTO=20
Test VALUES ( 'Iteration 1 | 1', 1, 0.6) <BR>and so on.<BR>There's a =
problem=20
with 'Iteration 1 | 1' because it's really several different data items. =
One=20
rule for setting up relational tables is that the column should contain =
'atomic'=20
item of data. <BR>Now, in order to get that first 1 in 'Iteration 1 | 1' =
you=20
have to use SUBSTR() to dig it out. It should be in a column by itself.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>What is stuff like '1 | 1' and '1 |=20
3' supposed to mean and be used for? It's very awkward to handle if =
it's=20
all in a single column.  </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>***********<BR>TEXT TO lcText =
noshow<BR>Iteration 1=20
| 1, 0, 0.5<BR>Iteration 1 | 1, 1, 0.6<BR>Iteration 1 | 1, 2, =
0.7<BR>Iteration 1=20
| 1, 3, 0.7<BR>Iteration 1 | 2, 0, 0.4<BR>Iteration 1 | 2, 1, =
0.3<BR>Iteration 1=20
| 2, 2, 0.3<BR>Iteration 1 | 2, 3, 0.4<BR>Iteration 1 | 3, 0, =
0.5<BR>Iteration 1=20
| 3, 1, 1.1<BR>Iteration 1 | 3, 2, 1.2<BR>Iteration 1 | 3, 3, =
0.3<BR>Iteration 1=20
| 4, 0, 0.7<BR>Iteration 1 | 4, 1, 0.4<BR>Iteration 1 | 4, 2, =
0.5<BR>Iteration 1=20
| 4, 3, 1.1<BR>Iteration 2 | 1, 0, 0.3<BR>Iteration 2 | 1, 1, =
0.7<BR>Iteration 2=20
| 1, 2, 0.4<BR>Iteration 2 | 1, 3, 0.7<BR>Iteration 2 | 2, 0, =
0.4<BR>Iteration 2=20
| 2, 1, 0.5<BR>Iteration 2 | 2, 2, 0.7<BR>Iteration 2 | 2, 3, =
0.3<BR>Iteration 2=20
| 3, 0, 0.6<BR>Iteration 2 | 3, 1, 0.7<BR>Iteration 2 | 3, 2, =
0.4<BR>Iteration 2=20
| 3, 3, 0.7<BR>Iteration 2 | 4, 0, 0.4<BR>Iteration 2 | 4, 1, =
0.5<BR>Iteration 2=20
| 4, 2, 0.7<BR>Iteration 2 | 4, 3, 0.3<BR>endtext=20
<BR>ALINES(aArr,lcText,1+4,',',CHR(13)+CHR(10))<BR>DIMENSION=20
aArr(ALEN(aArr,1)/3,3) <BR>CREATE CURSOR Test (iter Char(15), time int, =
valx=20
Num(4,1))<BR>INSERT INTO Test FROM ARRAY aArr </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>GO TOP IN Test<BR>BROWSE LAST NOWAIT=20
<BR></DIV></FONT>
<DIV><FONT size=3D+0><FONT face=3DArial color=3D#000080=20
size=3D2></FONT></FONT> </DIV>
<DIV><FONT size=3D+0><FONT face=3DArial color=3D#000080 size=3D2>SELECT=20
SUBSTR(iter,11,1) As iter, time , SUM(valx) FROM Test ;<BR>GROUP BY 1, =
time INTO=20
CURSOR test1 <BR></DIV></FONT></FONT>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Result: </FONT></DIV>
<DIV><FONT face=3DArial =
size=3D2>Iter Time      =20
 Sum_valx        =
 <BR>1  =20
          =20
0            =
 =20
2.1 <BR>1  =20
          =20
1            =
 =20
2.4 <BR>1  =20
          =20
2            =
 =20
2.7 <BR>1  =20
          =20
3            =
 =20
2.5 <BR>2  =20
          =20
0            =
 =20
1.7 <BR>2  =20
          =20
1            =
 =20
2.4 <BR>2  =20
          =20
2            =
 =20
2.2 <BR>2  =20
          =20
3            =
 =20
2.0 <BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>-Anders </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2></FONT><FONT=20
face=3DArial size=3D2></FONT><FONT face=3DArial=20
size=3D2></FONT><BR> </DIV></BODY></HTML>

------=_NextPart_000_0073_01C89AAF.4A3D6130--

John
Thu Apr 10 16:53:01 CDT 2008

Hi Anders,

I appreciate your help. Thanks! Can I extend my problem?
The dbf file I have is an output file from some program (output.dbf) so it's
already filled with 50 iterations and time 0 to 600. So I believe I can skip
the CREATE part. The Iteration x|y is just present in the file (output.dbf)
unfortunately. Also, except for the column val, there are more columns in
output.dbf (about 170 more). Is there some smart way to work on this file. I
really can't manually do a TEXT TO for all 50 iteration and 600 time steps.
Do you have some smart solution for this?

Kind regards,
John.

"Anders Altberg" wrote:

> Hi John
> I suggested you do a ouÃ¶d contain 'atomi
> CREATE CURSOR Test ( iter C(15), time Int, valx Numeric( 4,1)
> INSERT INTO Test VALUES ( 'Iteration 1 | 1', 0, 0.5)
> INSERT INTO Test VALUES ( 'Iteration 1 | 1', 1, 0.6)
> and so on.
> There's a problem with 'Iteration 1 | 1' because it's really several different data items. One rule for setting up relational tables is that the column should contain 'atomic' item of data.
> Now, in order to get that first 1 in 'Iteration 1 | 1' you have to use SUBSTR() to dig it out. It should be in a column by itself.
> What is stuff like '1 | 1' and '1 | 3' supposed to mean and be used for? It's very awkward to handle if it's all in a single column.
>
> ***********
> TEXT TO lcText noshow
> Iteration 1 | 1, 0, 0.5
> Iteration 1 | 1, 1, 0.6
> Iteration 1 | 1, 2, 0.7
> Iteration 1 | 1, 3, 0.7
> Iteration 1 | 2, 0, 0.4
> Iteration 1 | 2, 1, 0.3
> Iteration 1 | 2, 2, 0.3
> Iteration 1 | 2, 3, 0.4
> Iteration 1 | 3, 0, 0.5
> Iteration 1 | 3, 1, 1.1
> Iteration 1 | 3, 2, 1.2
> Iteration 1 | 3, 3, 0.3
> Iteration 1 | 4, 0, 0.7
> Iteration 1 | 4, 1, 0.4
> Iteration 1 | 4, 2, 0.5
> Iteration 1 | 4, 3, 1.1
> Iteration 2 | 1, 0, 0.3
> Iteration 2 | 1, 1, 0.7
> Iteration 2 | 1, 2, 0.4
> Iteration 2 | 1, 3, 0.7
> Iteration 2 | 2, 0, 0.4
> Iteration 2 | 2, 1, 0.5
> Iteration 2 | 2, 2, 0.7
> Iteration 2 | 2, 3, 0.3
> Iteration 2 | 3, 0, 0.6
> Iteration 2 | 3, 1, 0.7
> Iteration 2 | 3, 2, 0.4
> Iteration 2 | 3, 3, 0.7
> Iteration 2 | 4, 0, 0.4
> Iteration 2 | 4, 1, 0.5
> Iteration 2 | 4, 2, 0.7
> Iteration 2 | 4, 3, 0.3
> endtext
> ALINES(aArr,lcText,1+4,',',CHR(13)+CHR(10))
> DIMENSION aArr(ALEN(aArr,1)/3,3)
> CREATE CURSOR Test (iter Char(15), time int, valx Num(4,1))
> INSERT INTO Test FROM ARRAY aArr
> GO TOP IN Test
> BROWSE LAST NOWAIT
>
>
> SELECT SUBSTR(iter,11,1) As iter, time , SUM(valx) FROM Test ;
> GROUP BY 1, time INTO CURSOR test1
>
>
> Result:
> Iter Time Sum_valx
> 1 0 2.1
> 1 1 2.4
> 1 2 2.7
> 1 3 2.5
> 2 0 1.7
> 2 1 2.4
> 2 2 2.2
> 2 3 2.0
>
>
> -Anders
>
>

filtering data from a dbf file

RE: filtering data from a dbf file by John

Re: filtering data from a dbf file by Anders

Re: filtering data from a dbf file by John

Re: filtering data from a dbf file by Anders

Re: filtering data from a dbf file by John