understanding loops vfp
Hi: I have an issue with a table. THe table comprises dates and weeks. Attached as text file.
I need to 'fill' the missing weeks 38 and 39 with new records.
Here is the code I wrote:
USE "W:\backup\Backup\topten.d
GOTO top
n=1
FOR n=1 TO 12
SCATTER MEMO NAME loDataRecord
INSERT INTO "W:\backup\Backup\topten.d
replace date WITH date - 604800
replace week WITH week -1
n = n+1
ENDFOR && Ends loop
THe idea being that each week would be recreated, but reduced by one. I could then remove the duplicated weeks, leaving the weeks 38 and 39 as well as those I first had. What I do not understand is what is actually happening. I thought n to 12 would recreate 12 records, total 24. However i only get 3 more, total 15. The result actually works if I make n to 25; but cannot understand why. Moreover I think what is happening is that the first record is created BUT THE CURSOR MOVES TO THE NEW RECORD, then copies from there.
I am trying to figure out what will happen after week 52 and weeks start from 1 again. The table will be refreshed giving week 1 at the top. Using my code The FIRST copied record will be week 0. What will the NEXT copied record be? -1 or 51? I am going to test myself but hopefully you see my problem! ANd before anybody asks, I do not want to use the dates other than for the preliminary sort! They change too much to be valid !
Thanks!
topten.txt
I need to 'fill' the missing weeks 38 and 39 with new records.
Here is the code I wrote:
USE "W:\backup\Backup\topten.d
GOTO top
n=1
FOR n=1 TO 12
SCATTER MEMO NAME loDataRecord
INSERT INTO "W:\backup\Backup\topten.d
replace date WITH date - 604800
replace week WITH week -1
n = n+1
ENDFOR && Ends loop
THe idea being that each week would be recreated, but reduced by one. I could then remove the duplicated weeks, leaving the weeks 38 and 39 as well as those I first had. What I do not understand is what is actually happening. I thought n to 12 would recreate 12 records, total 24. However i only get 3 more, total 15. The result actually works if I make n to 25; but cannot understand why. Moreover I think what is happening is that the first record is created BUT THE CURSOR MOVES TO THE NEW RECORD, then copies from there.
I am trying to figure out what will happen after week 52 and weeks start from 1 again. The table will be refreshed giving week 1 at the top. Using my code The FIRST copied record will be week 0. What will the NEXT copied record be? -1 or 51? I am going to test myself but hopefully you see my problem! ANd before anybody asks, I do not want to use the dates other than for the preliminary sort! They change too much to be valid !
Thanks!
topten.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi beroni; thank you very much for your input. However the week() function runs from Jan-Dec; in the uk our tax year week runs april-march so I cannot use week(). Thanks anyway.
ASKER
Hi pcelba: I will give this a go, thank you very much. It looks like you are looping through weeks 0-52 and locating each in the table, then copying. Will try it out.
Just a few notes to VFP loops:
LOCAL n && Recommended because you are sure this variable cannot affect equally named variables in other modules in the calling chain
n=1 && Not necessary because the following command initializes the variable n
FOR n=1 TO 12
? n && Just to show the result
n = n+1 && This will skip each the second loop pass
ENDFOR && Ends loop ... This command increases the n by 1 (or by STEP defined in the FOR command)
You may copy the above code block and execute it in the Command Window. You may test what happens when you swap the first two lines.
Also some sources do not recommend to use one letter variables in VFP but it works.
LOCAL n && Recommended because you are sure this variable cannot affect equally named variables in other modules in the calling chain
n=1 && Not necessary because the following command initializes the variable n
FOR n=1 TO 12
? n && Just to show the result
n = n+1 && This will skip each the second loop pass
ENDFOR && Ends loop ... This command increases the n by 1 (or by STEP defined in the FOR command)
You may copy the above code block and execute it in the Command Window. You may test what happens when you swap the first two lines.
Also some sources do not recommend to use one letter variables in VFP but it works.
The code in the first post loops through 12 records at most. These 12 records are identified by the lnWeekNo in LOCATE command and inserted when the LOCATE is not successful.
lnWeekNo = 41 defines the starting week
and then the lnWeekNo is decreased by 1 in each loop pass.
lnWeekNo = 41 defines the starting week
and then the lnWeekNo is decreased by 1 in each loop pass.
ASKER
Thank you very much pcelba!
Here is the finished code, from the point I extract from the data:
CLOSE TABLES all
DO "W:\backup\Backup\qtopten.
CLOSE TABLES all
SELECT TOP 12 d.date, d.week FROM "W:\backup\Backup\toptenba
CLOSE TABLES all
SELECT TOP 1 d.date, d.week FROM "W:\backup\Backup\toptenba
CLOSE TABLES all
USE "W:\backup\Backup\toptenma
COPY TO ARRAY maxweek FIELDS week
COPY TO ARRAY maxdate FIELDS date
CLOSE TABLES all
USE "W:\backup\Backup\topten.d
lnWeekNo = maxweek
ltDateTime = maxdate
FOR n=1 TO 12
SELECT topten
LOCATE FOR week = m.lnWeekNo
IF !FOUND()
INSERT INTO topten (Date, week) VALUES (m.ltDateTime, m.lnWeekNo)
ENDIF
ltDateTime = m.ltDateTime - 604800
lnWeekNo = m.lnWeekNo -1
IF m.lnWeekNo = 0
m.lnWeekNo = 52
ENDIF
ENDFOR && Ends loop
Thank you very much!
Here is the finished code, from the point I extract from the data:
CLOSE TABLES all
DO "W:\backup\Backup\qtopten.
CLOSE TABLES all
SELECT TOP 12 d.date, d.week FROM "W:\backup\Backup\toptenba
CLOSE TABLES all
SELECT TOP 1 d.date, d.week FROM "W:\backup\Backup\toptenba
CLOSE TABLES all
USE "W:\backup\Backup\toptenma
COPY TO ARRAY maxweek FIELDS week
COPY TO ARRAY maxdate FIELDS date
CLOSE TABLES all
USE "W:\backup\Backup\topten.d
lnWeekNo = maxweek
ltDateTime = maxdate
FOR n=1 TO 12
SELECT topten
LOCATE FOR week = m.lnWeekNo
IF !FOUND()
INSERT INTO topten (Date, week) VALUES (m.ltDateTime, m.lnWeekNo)
ENDIF
ltDateTime = m.ltDateTime - 604800
lnWeekNo = m.lnWeekNo -1
IF m.lnWeekNo = 0
m.lnWeekNo = 52
ENDIF
ENDFOR && Ends loop
Thank you very much!
I can't understand why you use N to 12, but I wrote an example for you with the solution for your question.
You will see comments to create my table sample.
Please, take a look to the WEEK() function, it will help you with your week field (your values -1 or 51).
When you use FOR...ENDFOR, you do not need to add values to the variable (n=n+1).
*!* CREATE TABLE KKKK (FECHA T, SEMANA N(2))
*!* APPEND BLANK
*!* REPLACE FECHA WITH DATETIME(), SEMANA WITH WEEK(DATETIME(),0,0)
*!* INDEX ON FECHA TAG FECHA
USE kkkk
SET ORDER TO fecha DESCENDING
GOTO top
* crear fechas
SCATTER MEMVAR memo
ldFecInicio = fecha
FOR n=1 TO 12
ldSearchDate = ldFecInicio - (604800 * n)
lnWeek = WEEK( ldSearchDate,0,0 )
SEEK ldSearchDate
*LOCATE FOR fecha = ldSearchDate
IF EOF()
APPEND BLANK
GATHER MEMVAR MEMO
replace fecha WITH ldSearchDate , semana WITH lnWeek
ENDIF
ENDFOR