topic Problem in a LOOP in QlikView

Problem in a LOOP

datanibbler — Mon, 05 Sep 2016 09:09:46 GMT

Hi,

in the script I'm currently working on, there is a FOR_NEXT loop. In this instance, there are 43 records in the table I'm reading for the counter - so there are 43 items (>>Tracks<<), all of which share one common number (>>Serial_Lot_Number<<);

=> For each Track, I want to have a variable that I can consequently use to filter a table, and I want to incorporate the counter in the table_name, so I would end up with 43 tables.

The issue is, currently, the loop in itself works fine (I can step through it from 1 to 44), now I have just moved the closing >>NEXT i<< to after that LOAD I want to filter on the variable.

Something is wrong, however: Now, the first iteration runs fine, the table is called [tablename]_1 - on the second iteration, however, the variables are both set to NULL - and the LOAD is repeated again and again, always with the same filters and the same tablename ...

The relevant part of my script is this:

// Die Hilfstabelle mit den Tracks gehen wir jetzt durch und machen bei jedem Datensatz
// zuerst mal eine Variable aus dem Track, die wir später verwenden können.

FOR i = 1 TO $(v_Tracks_from_del)
LET v_current_Track = PEEK('Track', ($(i)-1), Aux_Tracks);
LET v_Serial_Lot = PEEK('Serial_Lot', ($(i)-1), Aux_Tracks);
// Die Schleife können wir natürlich erst dann wieder zumachen, wenn
// die gesamte Bearbeitung abgeschlossen ist.

// Jetzt laden wir die Transaktionstabelle einfach noch mal, um alle Buchungen rauszukriegen,
// die an dieser Ware vorgenommen wurden;
// Dabei filtern wir auf die Sachnr. und auf den aktuellen Track.

Transaktionen_Archiv_$(i):
LOAD
ITEM_NUMBER,
%ITEM_NUMBER,
TRAN_DATE_TD,
TRAN_DATE_Time,
TRAN_CODE,
QUANTITY,
BIN,
TRACKING_NUMBER,
CONTROL_NUMBER,
SERIAL_LOT_NUMBER,
shift
FROM
\\rgb1app202\production\01_QVD\QVD\TransDtl_A163_Archiv.qvd
(qvd)
WHERE SERIAL_LOT_NUMBER = '$(v_Serial_Lot)'
AND TRACKING_NUMBER = '$(v_current_Track)'
;

NEXT i

EXIT Script;

Can anyone help me there? Sorry I cannot post a sample of this main table that I want to filter, only of the small auxiliary_table which I use for the LOOP.

Thanks a lot!

Best regards,

DataNibbler

Re: Problem in a LOOP

marcus_sommer — Mon, 05 Sep 2016 09:34:02 GMT

Hi DataNibbler,

try it with single-quotes around the tablename within the peek-expression:

LET v_current_Track = PEEK('Track', ($(i)-1), 'Aux_Tracks');
LET v_Serial_Lot = PEEK('Serial_Lot', ($(i)-1), 'Aux_Tracks');

- Marcus

Re: Problem in a LOOP

datanibbler — Mon, 05 Sep 2016 09:38:27 GMT

Hi Marcus,

as always you were right. It works now. But why did it work halfway before - the LOOP (small version, with the NEXT right after the variable_definitions) worked and I could step through it, but as soon as I widened it to encompass the LOAD, it broke ...

I get noch PEEKels from this PEEK_function ...

Re: Problem in a LOOP

marcus_sommer — Mon, 05 Sep 2016 09:46:36 GMT

Hi DataNibbler,

in general each peek-statement which is outside from a load needs to be specified the table from where the data comes from. If not a right table(-syntax) is specified the last loaded table will be used (is a not documented default-behaviour) but after your first loop-run your Aux_Tracks table isn't the last loaded table anymore and therefore return the following peek-expressions NULL.

- Marcus

Re: Problem in a LOOP

datanibbler — Mon, 05 Sep 2016 09:49:39 GMT

Right again 😉

Thanks a lot!

Best regards,

DataNibbler