Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loops and scoping

Hi there!

To put this straight I'm having a trouble with a table being extracted before for-looping. Table I am talking about get's extracted well but after the first iteration of for loop it get's lost and so do the data I need to work with in this loop.:

My code looks like this:

connections:

LOAD Connection

FROM

(ooxml, embedded labels, table is Sheet1);

LET NumRows=NoOfRows('connections');

FOR conn = 1 to $(NumRows)

    LET conStr = Peek('Connection', $(conn), connections);

    OLEDB CONNECT TO $(conStr) (XPassword is OEdObQVNHE);

   

    CALL loadData

   

    DISCONNECT;

NEXT conn

store SoftwareResults into [$(vQvdPath)SoftwareResults.qvd]

(qvd);

Basically this is for extracting the same set of data from multiple connections. Extract procedure is defined in the other tab and is called 'loadData'.

I'm adding screenshots from debugger.

So why is the 'connections' table <null> after the first loop iteration? how to avoid this?
Thank you for your comments!

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

I have been struggling with a peek that ended up trashing my Saturday morning () that sounds familiar.

First observation I always find I have to put the table name in single quotes, but maybe that is just me.

Second thing was I didn't have the table name initially and as I was also doing something with my key (that was a date, but equivalent of your Connection) in my sub (that was an equivalent of your loadData) it seemed to be resetting my list of values when it looped back around.

I am not sure if that helps or not.

View solution in original post

6 Replies
chrismarlow
Specialist II
Specialist II

I have been struggling with a peek that ended up trashing my Saturday morning () that sounds familiar.

First observation I always find I have to put the table name in single quotes, but maybe that is just me.

Second thing was I didn't have the table name initially and as I was also doing something with my key (that was a date, but equivalent of your Connection) in my sub (that was an equivalent of your loadData) it seemed to be resetting my list of values when it looped back around.

I am not sure if that helps or not.

Not applicable
Author

no, peek works well, problem is that after first iteration pass table is null...

chrismarlow
Specialist II
Specialist II

Hmmm, despite that it does sound familiar. That is what I was seeing, abridged code;

hitting AMNT_DATE.PNG
So before I added the table (3, as I say with single quotes) because I was doing something (2 with a separate table) to AMNT_DATE, I was then seeing null on second pass of my field AMNT_DATE (1) even though I could store 'dates' or stop the app and look at it & see that I had a set of dates.

Not applicable
Author

sorry Christopher, I have tried single quotes on table name in peek and this corrects the problem. Connections table is no longer null.

Thank you!

Another magic of qlik worked out...

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Christopher, I think your first observations is the correct one. The first time around that the engine encounters the peek() function, it will return a correct value because the third parameter is no parameter and peek() will fall back to the table that was most recently loaded, e.g. connections. Call it beginners luck.

Inbetween the first and the second loop, various other tables have been loaded, and now the peek() function won't find a field called 'Connection' in the table that was most recently loaded. Therefor, NULL shall be thy reward. Unfortunately.

Greengold, put single quotes around the third parameter and try again. It's all in the details (mostly in the (lack of) punctuation marks)

Best,

Peter

chrismarlow
Specialist II
Specialist II

Ah. Thank you for the good explanation ... that makes sense now (which will make it easier to remember for next time & hopefully save my weekend!).