SQL in FOR Loop seems to corrupt Inline Table

[#1]

In the FOR Loop below I set two variable, vBox and vVolCnt, using "peek" to pull the values from the inline table called BOX.  Each iteration I pull the next set of values until I've looped through the whole table.  This logic worked perfectly until I added the SQL Select inside the loop.  Now only the first iteration gets the proper values from the "peek" operations, every susbsequent iteration gets Nulls for both values.  Trying to understand why the SQL would effect the peeks and how to resolve this?

BOX:

LOAD * INLINE [BOXName, VOLCnt
"MRC", 49152
"TTN", 49152
"PLT", 12288
"GLD", 12288
"SVR", 12288
"IRN", 12288
];

LET NumRows=NoOfRows('BOX');
FOR i=0 to $(NumRows)-1
LET vBox=Peek('BOXName',$(i),BOX);
LET vVolCnt=peek('VOLCnt',$(i),BOX);
LET my_include= '..\..\Include\BDS-IT\odbc-' & '$(vBox)' & '.qvs';
$(include=$(my_include));

DISC_USAGE:
SQL SELECT
     '$(vBox)' AS SRC_SYS_CD,
     DISC_DATE,
     VOL_TYPE,
     MIN_FREE_TB,
     MAX_FREE_TB,
      ...
     FROM   NEO.DISK_USAGE_VIEW           

     WHERE
              VOLUME_NAME LIKE '$FC%'
               ...

      HAVING

             VOL_CNT = $(vVolCnt)
       FOR READ UNCOMMITTED ACCESS IN SHARE MODE;
NEXT;

[#2]

I just tried moving the SQL into its own subroutine thinking this may solve the problem....made no difference

SUB GET_DISC_USAGE (box, volcnt)

  DISC_USAGE:

  SQL SELECT
      '$(vBox)' AS SRC_SYS_CD,
      DISC_DATE,
      VOL_TYPE,
      MIN_FREE_TB,
      MAX_FREE_TB,
       ...
      FROM   NEO.DISK_USAGE_VIEW           

      WHERE
               VOLUME_NAME LIKE '$FC%'
                ...

       HAVING

              VOL_CNT = $(vVolCnt)
        FOR READ UNCOMMITTED ACCESS IN SHARE MODE;

END SUB;

BOX:

LOAD * INLINE [BOXName, VOLCnt
"MRC", 49152
"TTN", 49152
"PLT", 12288
"GLD", 12288
"SVR", 12288
"IRN", 12288
];

LET NumRows=NoOfRows('BOX');
FOR i=0 to $(NumRows)-1
  LET vBox=Peek('BOXName',$(i),BOX);
  LET vVolCnt=peek('VOLCnt',$(i),BOX);
  LET my_include= '..\..\Include\BDS-IT\odbc-' & '$(vBox)' & '.qvs';
  $(include=$(my_include));
  CALL GET_DISC_USAGE (vBox, vVolCnt); 
NEXT;

[Caique_Zaniolo · Accepted solution]

Hi Lew,

try putting a single quote around BOX in your Peek function. Field name and table name must always have quotes wrapping them.

  LET vBox=Peek('BOXName',$(i),'BOX');
  LET vVolCnt=peek('VOLCnt',$(i),'BOX');

[rlp]

You should insert the keyword

NoConcatenate

between the declaration of the second table and its LOAD instruction.

Otherwise, QV will insert the new lines in the first table, which explains the Null.

Regards.

[Caique_Zaniolo]

Hi Richard,

NoConcatenate won't help in this case. The two tables are not being concatenated. QlikView only auto concatenates tables if they have the same number of fields and with the same name.

Run this script and you will see it fixes the problem (quotes around the table name in the Peek function).

BOX:

LOAD * INLINE [

          BOXName, VOLCnt

          "MRC", 49152

          "TTN", 49152

          "PLT", 12288

          "GLD", 12288

          "SVR", 12288

          "IRN", 12288

];

LET NumRows=NoOfRows('BOX');

FOR i=0 to $(NumRows)-1

          LET vBox=Peek('BOXName','$(i)','BOX');

          LET vVolCnt=peek('VOLCnt','$(i)','BOX');

          Test:

          LOAD

                    '$(vBox)' AS SRC_SYS_CD,

                    Rand()            AS DISC_DATE

          AutoGenerate (1);

NEXT

[#6]

Caique,

That did it! 

Thanks you very much for your assistance.  It always seems to be the simple things that I overlook

Cheers,

Lew