topic Re: Loops and scoping in QlikView

Loops and scoping

Mon, 16 Mar 2015 13:24:20 GMT

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!

Re: Loops and scoping

chrismarlow — Mon, 16 Mar 2015 13:40:57 GMT

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.

Re: Loops and scoping

Mon, 16 Mar 2015 13:47:16 GMT

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

Re: Loops and scoping

chrismarlow — Mon, 16 Mar 2015 13:59:44 GMT

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

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.

Re: Loops and scoping

Mon, 16 Mar 2015 14:12:20 GMT

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

Re: Loops and scoping

Peter_Cammaert — Mon, 16 Mar 2015 14:14:38 GMT

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

Re: Loops and scoping

chrismarlow — Mon, 16 Mar 2015 14:25:21 GMT

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!).