Help with Looping Construct

darrellbutler — Tue, 07 Jan 2014 16:29:47 GMT

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

Re: Help with Looping Construct

bbi_mba_76 — Tue, 07 Jan 2014 16:59:21 GMT

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)'

;

Re: Help with Looping Construct

darrellbutler — Tue, 07 Jan 2014 17:31:12 GMT

Grazie ! Many Thanks this excellent !

topic Re: Help with Looping Construct in QlikView

Help with Looping Construct

Re: Help with Looping Construct

Re: Help with Looping Construct