Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |
Grazie ! Many Thanks this excellent !