Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

for...next with unknown boundry(s)

Continuing explore for...next and stumbeld upon another issue. What if I don't know my for boundries? The max value could be 216 or 345 or 555. Is  it possible to set for i = 1 to 1000 and then handle the error in the script? Actually ignore error and LOAD nothing?

1 Solution

Accepted Solutions
MVP
MVP

for...next with unknown boundry(s)

Thomas,

maybe you could use ScriptError and errormode together with proposed Exit for?

set ErrorMode=0;            // will prevent from stopping script in case of error

for vWeek= 1 to 52
for vTrans=1 to 500
LOAD colT1,
  colT2
FROM
[http://yourdomain.com/history/weeks.asp?qsWeek=$(vWeek)&trans=$(vTrans)]
(html, codepage is 1252, embedded labels, t
able is @2);

exit for when ScriptError <>1;

next
next

5 Replies

Re: for...next with unknown boundry(s)

Hi Thomas,

What about using EXIT FOR?

FOR i = 1 TO 2000

    Table:

    LOAD $(i) AS Field

    AUTOGENERATE 1;

    LET vMaxValue = Peek('Field', -1, 'Table');

    EXIT FOR WHEN $(vMaxValue) >= 100;

NEXT

This is pretty dummy, but you can get the idea.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable

Re: for...next with unknown boundry(s)

Miguel, pasting a mockup of my script to explain a bit more.

for vWeek= 1 to 52
for vTrans=1 to 500

LOAD colT1,
  colT2
FROM
[http://yourdomain.com/history/weeks.asp?qsWeek=$(vWeek)&trans=$(vTrans)]
(html, codepage is 1252, embedded labels, table is @2);

next
next


I don't know how many trans there are. It could be 2 or 234 or 478. Lets say that it is 234 -when the for loop reaches 235 and tries to load that, an error occures because there are no such url. When that happens I would like to loop to 500 (without errors and no saving (loading) so that we loop vWeek and start all over again.

Even more complicated since they use leading zeros so trans #1 is really 001 and so on. Sorted that out with if..elseif..else but it's a lot of nestling.

MVP
MVP

for...next with unknown boundry(s)

Thomas,

maybe you could use ScriptError and errormode together with proposed Exit for?

set ErrorMode=0;            // will prevent from stopping script in case of error

for vWeek= 1 to 52
for vTrans=1 to 500
LOAD colT1,
  colT2
FROM
[http://yourdomain.com/history/weeks.asp?qsWeek=$(vWeek)&trans=$(vTrans)]
(html, codepage is 1252, embedded labels, t
able is @2);

exit for when ScriptError <>1;

next
next

phcaptjim
Contributor

Re: for...next with unknown boundry(s)

I have a similar issue.

I'm reading a table where I can only pull 50k records at a time due to the limitations of an ODBC driver with a cloud based application.  Right now I am counting the number of records so I know how many times to perform the loop.  My issue is the simple count of records is taking a very long time (SELECT COUNT(*) FROM TABLE).  I want to eliminate that step and just run the loop and have it stop when all the records have been read.

How would I do that?  It seems so simple but I can't find the solution.

Thanks!

MVP & Luminary
MVP & Luminary

Re: for...next with unknown boundry(s)

You could consider if incremental loads are possible - Incremental Load. If not you could look to a record-id from this table - maybe through meta-data from system-tables from these database. Also could a split from this task into two tasks help to improve your load-times. Then the first step to generate only the record-count could run before and parallel to other tasks.

- Marcus