Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrellbutler
Contributor

Help with Looping Construct

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

1 Solution

Accepted Solutions
bbi_mba_76
Valued Contributor

Re: Help with Looping Construct

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
2 Replies
bbi_mba_76
Valued Contributor

Re: Help with Looping Construct

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
Contributor

Re: Help with Looping Construct

Grazie ! Many Thanks this excellent !

Community Browser