topic Re: SQL in FOR Loop seems to corrupt Inline Table in QlikView

SQL in FOR Loop seems to corrupt Inline Table

Mon, 04 Feb 2013 15:38:19 GMT

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;

Re: SQL in FOR Loop seems to corrupt Inline Table

Mon, 04 Feb 2013 16:58:38 GMT

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;

Re: SQL in FOR Loop seems to corrupt Inline Table

Caique_Zaniolo — Tue, 05 Feb 2013 12:45:20 GMT

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

Re: SQL in FOR Loop seems to corrupt Inline Table

rlp — Tue, 05 Feb 2013 13:54:18 GMT

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.

Re: SQL in FOR Loop seems to corrupt Inline Table

Caique_Zaniolo — Tue, 05 Feb 2013 14:59:27 GMT

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

Re: SQL in FOR Loop seems to corrupt Inline Table

Tue, 05 Feb 2013 15:02:33 GMT

Caique,

That did it!

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

Cheers,

Lew