DO...LOOP exits too quickly

[jvleensel]

Hi Community,

I have a case where I actually don't understand the behavior.

i have a loop with a Counter as a variable that is incremented of 1 each cycle in the loop.

The condition is Do while vCounter < vMaxLoop whre vMaxLoop is set to 105.

on execution once the vCounter is incremented to 10 then the loop stops.

Here below a piece of the code:

Do while vCounter<vMaxLoop

...

LET vCounter=$(vCounter) + 1;

Loop;

I have already tried to use the $ sign in the condition, use the Num() function to convert the value to integer without success.

Have you already encountered such behavior and how did solve the issue?

thank you beforehand.

Jérémie

[jensmunnichs]

Hi Jérémie,

I just created an application with the script below, and it looped just fine:

LET vMaxLoop = 105;
LET vCounter = 0;

Do while vCounter<vMaxLoop

TRACE $(vCounter);

LET vCounter=$(vCounter) + 1;

Loop;

Can you verify whether that works for you, and also whether your vMaxLoop is actually set to what you think it should be (105), and whether you are resetting the vCounter variable before the loop?

[jvleensel]

Hi,

LET vCounter=0; is done 

The 105 is based on the count of records in a table. I use the Peek function to get the count and use LET vMaxLoop = Peek('Myfield'); I don't know whether it's important to mention it.

In the loop there is a IF statement that checks whether vCounter = 0 then Store data in QVD 1  else Select data from QVD 1 and concatenate + store in QVD1 (incremental load approach).

With the TRACE I see well that the loop is executed 9 times and as soon as vCounter = 10 the loop stops.

it seems that the 105 of VMaxLoop is not fully treated as 105 and as soon as vCounter = 10 the condition is fullfilled where it should not.

I've tried with another table where the count of records is 5400 and as soon as vCounter = 540 then the loop stops.

hope this helps clarifying the issue I face.

[jensmunnichs]

I'm no expert on Peek(), but doesn't Peek('Myfield') just give you the last value loaded for 'Myfield', and not the number of records in a table? Or is this a table that holds every tablename + number of rows per table? If not, maybe try using the NoOfRows function to set the vMaxLoop value.

If that's not it, have you checked whether the value for vMaxLoop is correct? If not, you can do this by checking the Variable overview or simply by tracing the variable like you did with vCounter.

[jvleensel]

I've used a TRACE on both variables to track the value and all seems working as expected except this end of loop where the condition is not fulfilled 🙂

[jensmunnichs]

To be honest, I'm not too sure what's causing this. Is there any condition to incrementing vCounter? If not, maybe try using a For... Next loop instead, see if that gives you the same result.

For vCounter = 1 to $(vMaxLoop)

<Your expression>

Next

Another thing to try would be to simply create a text object after reloading and test if vMaxLoop = vCounter. If they look like they have the same value but this still returns FALSE, there is a problem in formatting, in which case, try changing vMaxLoop = Peek('Myfield') to vMaxLoop = Num(Peek('Myfield')). I know you said you tried converting the value, but I'm not sure if you were talking about vMaxLoop or vCounter (or both). Same goes for encapsulating the variable in $() in the condition (just vMaxLoop, not both).

If all that doesn't work either, it would help a lot if you could just post your entire script (with scrambled table/field names if you wish). If that's not possible I'm not sure I can help you, I'm running out of ideas, sorry 😞

[jvleensel · Accepted solution]

Hi and really thank you for your support. It's appreciated.

I've commented all the code inside the For Loop and the loop works as expected.

The issue is with a variable that is set to a SQL Statement and that seems not properly generated on iteration 10 but this did not generate any error in Qlik Sense until I execute the generated SQL in Oracle SQL Developer.

Post can be closed and thanks again for your time.