assigning record field to variable [Wall of Text]

[cmjtf1re]

Greetings, I have a question that I am not sure of how to ask. This prevents me from accurately searching for a solution, therefore I must rely on good ole fashion verbiage...or in this case Typiage.

I have a database with 10 questions in it. Each question may contain 1 through 10 in the field.

I'm trying to write a program that will basically count for question 1 = 1, 2, 3, 4, and so on. then continue to count for question 2 = 1, 2, 3, 4, and so on. All the way to question 10. My code works perfectly fine for counting only one question's field, ie; count only for question 1 = 1, 2, 3, 4...

Also, the name of my question fields are ques1, ques2, all to ques10 subsequentially.

Here is my code:

do while inc < 11
go top
store "ques"+allt(str(inc)) to mques
* the single line above is where I'm trying to
* assign 'ques1' to mques, all the way to 'ques10'

do while not eof()
store mques to mqt
* the single line above is where I would
* normally type 'store ques1 to mqt'
* which would store whatever is in the ques1
* field to mqt. ie;if ques1 = 2, mqt now = 2

store 0 to mput
do while not eof() and mques = mqt
* the latter half of the single line above
* is where I would type 'ques1 = mqt'
* which if it worked the way I needed it to
* would mean if ques1 has '2' in the field,
* continue.

mput = mput + 1
? mques, mput
skip
enddo
select 2
appe blank
replace re1_10 with mques+mqt, actual with mput
* dont mind the indentation of the single
* line above, this is where I want re1_10
* to say 'ques1' + '1' thru '10', to
* 'ques 10' + '1' thru '10'
select 1
enddo
inc = inc+1
enddo

//end code

My problem is I'm trying to cut corners instead of writing 10 duplicate lines of code for 10 questions to do what could be done with 1 set of code.

What keeps happening is the line 'store ques1 to mqt' would normally store the value of that particular record to mqt so if ques1 was 2, mqt would then be 2. but because I use 'store "ques"+allt(str(inc)) to mques' mques goes through the cycle ten times, each time increasing inc: ques1, ques2, ques3 and so on, but the line 'store mques to mqt' doesn't store the value of ques1, or ques2 and up, instead it makes mqt actually equal the word 'ques1' instead of the value.

This is my issue and this is what I am hoping you can help me with.

ps I tried storing 0 to mqt before storing mques to mqt to make it register mqt as a numeric field, but still it does not work. I'm going to continue trying to figure it out but help would be greatly appreciated.

Thanks.

 

[cmjtf1re]

Tamar I like to avoid array's if at all possible.

This works:

CLEAR
SELECT 2 
USE (TempCount) EXCL
SET SAFETY OFF
ZAP
FOR nQuesCount = 1 TO 10
	APPE BLANK
	REPLACE q WITH nQuesCount
	FOR nValCount = 1 to 13
		STORE "v" + ALLT(STR(nValCount)) TO m.numValue
		REPLACE (m.numValue) WITH 0
	ENDFOR
ENDFOR
SET ORDER TO q
SELECT 1
USE ?
INDEX ON response+ques1+ques2+ques3+ques4+ques5+ques6+ques7+ques8+ques9+ques10 TO c:\windows\temp\qcount
SET SAFETY ON
SET FILTER TO termcd = "SC"
SCAN
	FOR incQuesResp = 1 TO 13
		numIncValue = "v" + ALLT(STR(incQuesResp))
		FOR nQCount = 1 TO 10
			STORE "ques" + ALLT(STR(nQCount)) TO m.strQues
    		mQues = EVAL(m.strQues)
			DO CASE
			   CASE VAL(mQues) = incQuesResp
			      SELECT 2
			      SEEK (nQCount)
			      tempVar = EVAL(numIncValue)+1
			      REPLACE (numIncValue) WITH tempVar
			      SELECT 1
			ENDCASE
		ENDFOR
	ENDFOR
ENDSCAN
SELECT 2
GO TOP
BROW &&title "pres ESC to exit - close browser to recount" nolgrid norgrid noedit nodelete norefresh noappend FONT 'Courier',12

Thanks for all your help guys.

 

[Imaginecorp]

My 2 cents; with the above code you will be waiting hours if using a table with over a 100,000 + records...Great for a small table, 100 or so records... Sorry

 

[cmjtf1re]

Depending on which machine you decide to run it on it could take a good length. For what we need it for and the machines we'll be running it on it's not time consuming.

200k records took about 10 seconds.

 

[Olaf Doschke]

Let me just repost your sourcecode commented:

Clear
* I'm missing variable declarations here. Because you have none you intr
SELECT 2 && prefer SELECT 0, if you need an empty workarea
USE (TempCount) EXCL && TempCount "falls from the sky" - where is that set?
SET SAFETY OFF
Zap && You zap the "TempCount" table, it seems you want a temp table. Cursors are there for this
* CREATE CURSOR curTempCount (...field definitions...q,v1,...,v13)
* Later On I see what you rather want is an Array. I know you want to prevent an array,
* but you can browse the array in the end, if you append it to a cursor just before
* the BROWSE.
FOR nQuesCount = 1 TO 10
    * INSERT INTO instead of APPEND BLANK and several following REPLACEs would be better.
    * At least make sure you SET TALK OFF, to prevent a message in the status bar for *each* REPLACE.
    APPE BLANK 
    REPLACE q WITH nQuesCount  
    FOR nValCount = 1 to 13
        STORE "v" + ALLT(STR(nValCount)) TO m.numValue
        REPLACE (m.numValue) WITH 0
    ENDFOR
ENDFOR
SET ORDER TO q && After the loop that would always be the highest number (in this case 10)
SELECT 1 && again prefer SELECT 0
USE ? && very bad instructed interactivity...
* I assume you know this is not end user friendly, as the user needs to know what table to select.
INDEX ON response+ques1+ques2+ques3+ques4+ques5+ques6+ques7+ques8+ques9+ques10 TO c:\windows\temp\qcount
SET SAFETY ON

* The following could be replaced by an SQL-Select.
SET FILTER TO termcd = "SC"
SCAN && For termcd = "SC"
    FOR incQuesResp = 1 TO 13
        numIncValue = "v" + ALLT(STR(incQuesResp))
        FOR nQCount = 1 TO 10
            * The next 2 lines could also be done at once: mQues = Eval("ques"+Transform(nQCount))
            STORE "ques" + ALLT(STR(nQCount)) TO m.strQues
            mQues = EVAL(m.strQues) 
            * here instead update an array element of a 2 dimensional array: 
            * something like laCount[nQCount,mQues]=laCount[nQCount,mQues]+1
            * no need to loop incQuesResp from 1 to 13, as mQues determines 
            * what counter array element needs an increment.
            DO CASE
               * A CASE with only a single CASE is an IF..ENDIF, if OTHERWISE would be in it, that's an ELSE branch.
               CASE VAL(mQues) = incQuesResp && mQues is already a number, isn't it? Or are quesX fields character fields?
                  SELECT 2 
                  SEEK (nQCount)
                  tempVar = EVAL(numIncValue)+1
                  REPLACE (numIncValue) WITH tempVar
                  SELECT 1
            EndCase
            * Don't loop incQuesResp, instead set numIncValue = "v"+TRANSFORM(EVAL(m.strQues)).
        ENDFOR
    ENDFOR
ENDSCAN
SELECT 2
GO TOP
BROW &&title "pres ESC to exit - close browser to recount" nolgrid norgrid noedit nodelete norefresh noappend FONT 'Courier',12

You need to practice with Eval(), Val() to see the difference and what transformations are done. Also take some time to learn name expressions and makro substitution (and why to only use it when val(), eval() or name expressions will also do the job).

I wonder if it's really 200K records with the filter termcd = "SC", or were you referring on the total number of records? I'd say 10 seconds is slow for this kind of job. All in all you just want some counts.

I support Tamar in that a normalization of the data would make it much easier.

Bye, Olaf.