Help with Looping Construct

[darrellbutler]

I want to execute the following piece of code several times based on the number of customer orders for a given period. These customer order numbers will be stored in variables (not shown in code snippet)

What looping construct can I use to dynamically calculate the variables in step 2 and then generate the qvd table in step 3 .

//need to loop through this code to create variables

yield:

LOAD ORDER_NO,

     VATS_MADE,

     NUMBER_OF_BLOCKS,

     NUMBER_OF_BLOCKS / VATS_MADE as BLOCKS_PER_VAT;

LOAD mid(ORDER_NO, 2,6) as ORDER_NO ,

     VATS_MADE,

     NUMBER_OF_BLOCKS

FROM

G:\2_QVDLayer\Creamery_Production\FULL_CREAMERY_PRODUCTION_YIELD.qvd

(qvd);

//STEP 2 store variables based on data extracted from step above

LET vNoBlockPerVat = FieldValue('BLOCKS_PER_VAT',1);

LET vNoOfVats      = FieldValue('VATS_MADE', 1);

LET vOrder         = FieldValue('ORDER_NO',1) ;

DROP Table yield;

//STEP 3 using variables populated above create a qvd

VATS:

LOAD ORDER_NO,

         VATS,

         VATS * $(vNoBlockPerVat) as STARTING_BLOCK_NUMBER;

LOAD '$(vOrder)' as ORDER_NO,

          recno() as VATS    

          AutoGenerate $(vNoOfVats) ;

STORE * from VATS into g:\2_QVDLayer\vats; 

Concatenate

LOAD * From   g:\2_QVDLayer\vats;

Many thanks for any help offered.

Darrell

[bbi_mba_76 · Accepted solution]

Hi,

maybe like this:

Tb1:

SQL select....

    ;

    LET v_rowTemp = NoOfRows('Tb1'); // get the total number of rows in table

    for i=1 to $(v_rowTemp)    

        LET vBLOCKS_PER_VAT = peek('BLOCKS_PER_VAT',$(i)-1,'Tb1');

        .....

     Tb2:

        SQL

            Select ...

            WHERE

                  vBLOCKS_PER_VAT = '$(vBLOCKS_PER_VAT)'

               ;

next

[darrellbutler]

Grazie ! Many Thanks this excellent !