topic Re: Looping through Table Rows in QlikView

Looping through Table Rows

Fri, 01 Feb 2013 19:46:20 GMT

I want to Loop through each row in a table, set variables to the values of each row in order to do some repetitive processing. Seems like a simple construct that would be used often. Here is what I thought would work, but for some reason the data coming back doesn't seem to match across the column for each row returned, and as I work through index by third iteration I'm getting nulls on the second column.

BOX:

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

LET NumRows=NoOfRows('BOX');
FOR i=1 to $(NumRows)
LET vBox=FieldValue('BOXName',$(i));
LET vVolCnt=FieldValue('VOLCnt',FieldIndex('BOXName','$(vBox)'));
//do some stuff....
NEXT;

Result set for variables after run:

vBox	vVolCnt
MRC	49152
TTN	12288
PLT	<NULL>
GLD	<NULL>
SVR	<NULL>
IRN	<NULL>

Seems I'm missing something very elementary here

Re: Looping through Table Rows

danielrozental — Fri, 01 Feb 2013 19:57:49 GMT

You should be using Peek instead,

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

LET vVolCnt=Peek('VOLCnt',$(i));

Re: Looping through Table Rows

Oleg_Troyansky — Fri, 01 Feb 2013 20:01:14 GMT

.. and specify the table name in your peak function, as a third parameter

Re: Looping through Table Rows

Fri, 01 Feb 2013 20:10:13 GMT

Daniel,

Thanks for the quick reply. It seems I totally overthought this one

Peek with the index works!

Cheers,

Lew

Re: Looping through Table Rows

Fri, 01 Feb 2013 20:16:29 GMT

Oleg,

It works perfect without a third parameter of table name, but for grins I tried adding this third parameter...it didn't break anything, but the result was the creation of a new variable of the same name as table that would get set to NULL on each peek.

Daniel's suggestion worked as is, although seem tables have origin 0 not 1 in QV, so had to change my FOR:

LET

NumRows=NoOfRows('BOX');
FOR i=0 to $(NumRows)-1
LET vBox=Peek('BOXName',$(i));
LET vVolCnt=peek('VOLCnt',$(i));
//do some repetitive stuff....
NEXT;

Re: Looping through Table Rows

Oleg_Troyansky — Fri, 01 Feb 2013 20:32:04 GMT

I might be over thinking it, but it might be important when you have the same value repeating multiple times in different tables rows, and possibly the same field present in multiple tables. then, when you specify

peek ('field', index)

it becomes unclear what the index should represent - the value index in a field list, or a row in a table? ...what table?

the two formats might work identically in simple cases when the field only exists in the same table, and when values are fairly unique.

Re: Looping through Table Rows

Fri, 01 Feb 2013 22:03:50 GMT

Well Oleg, thought you might be onto something here. Once I put an SQL Select into the loop, the "Peeks" returned NULL on the second iteration as though somehow the SQL statement cleared the values from the Inline Table. Started thinking your qualification with the third parameter might be the ticket, but didn't make a difference

Any thoughts on why peek is acting differently now that there is an SQL statment in the mix?

LET NumRows=NoOfRows('BOX');
FOR i=0 to $(NumRows)-1
LET vBox=Peek('BOXName',$(i));
LET vVolCnt=peek('VOLCnt',$(i));
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: Looping through Table Rows

Oleg_Troyansky — Sat, 02 Feb 2013 02:27:15 GMT

Your SQL statement appears after the peek()... you don't show the part of the script tha loads the previous table...

The SQL statement by itself shouldn't change much. If your previous statement is also a new SQL, - check if your fields have the same upper-lower case as you spelled them in the peek(). It looks like your SQL only returns upper case fields...

Re: Looping through Table Rows

Sat, 02 Feb 2013 03:32:55 GMT

The only previous statement is the INLINE LOAD....so very puzzled Doesn't seem like the SQL SELECT should have any effect on these "Peeks", but it does.

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: Looping through Table Rows

torstein — Mon, 28 Sep 2015 13:15:03 GMT

Peek is way to slow. How do I loop trough the data in a faster way?

Re: Looping through Table Rows

Thu, 28 Jan 2016 15:11:43 GMT

What about when you use the value from one to find the row in a different table?

Re: Looping through Table Rows

Anonymous — Sat, 20 May 2017 22:24:25 GMT

Was there any final conclusion on this one? I have an example where I did not use the third parameter, and I got unexpected results. To be safe, and like Henric says here and here, I will use a third parameter; the name of the Table where I expect to find that Field.

Re: Looping through Table Rows

SebSz — Tue, 22 Oct 2019 09:41:57 GMT

Great solution! Just the count of the variable i should start from 0:

BOX:

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

LET NumRows=NoOfRows('BOX')-1;
FOR I=0 to $(NumRows)

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

LET vVolCnt=Peek('VOLCnt',$(i));//do some stuff....
NEXT;

Let vBox= Null();
Let NumRows= Null();
Let I = Null();

Re: Looping through Table Rows

Harry_M — Tue, 03 Mar 2020 22:46:08 GMT

Like if you still come to this thread to copy and paste the For loop instead of typing it out. 😁