Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL in FOR Loop seems to corrupt Inline Table

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;

1 Solution

Accepted Solutions
Caique_Zaniolo
Employee
Employee

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

View solution in original post

5 Replies
Not applicable
Author

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
Employee
Employee

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
Creator
Creator

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
Employee
Employee

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

Not applicable
Author

Caique,

That did it! 

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

Cheers,

Lew