topic Re: Loop to total rows in App Development

Loop to total rows

pauldamen — Fri, 29 Dec 2017 07:42:42 GMT

All,

I have a table which I retrieve in JSON format. This works with a skip and take function, so in a loop I change the skip to be able to loop through the table.

My problem is the To part of the loop. Because the take has a 5000 record limit if I use a function like NoOfRows('Table') it stops after the first loop because it sees the 5000. What I can use to let the loop run to 22.000 (which is now the number of records in the table)?

Regards, Paul

Re: Loop to total rows

pradosh_thakur — Fri, 29 Dec 2017 07:59:02 GMT

Not sure if this gonna help but can you use rowno() and get its maximum value stored in a variable or use it directly?

Re: Loop to total rows

pauldamen — Fri, 29 Dec 2017 08:00:22 GMT

What would I need to do to try this suggestion?

Re: Loop to total rows

marcus_sommer — Fri, 29 Dec 2017 08:18:14 GMT

It sounds that it might be solvable with another loop which sliced your input-table. There you could add a counter maybe with something like:

ceil(rowno() / 5000) as LoopCounter

and then you could loop through this field like:

for i = 1 to fieldvaluecount(LoopCounter)

FinalTable:

load * from Table where LoopCounter = $(i);

... your other stuff ...

- Marcus

Re: Loop to total rows

pauldamen — Fri, 29 Dec 2017 09:51:34 GMT

Problem is I need tot loop to load the whole table since the source only allows me to get 5000 records at once.

So in order to load all records I need a loop statement, but in order to make the loop statement I need all records. So that is what I am sort of stuck in now.

Re: Loop to total rows

Anonymous — Fri, 29 Dec 2017 10:24:48 GMT

Sounds like you need to look at pagination - here is the url of to the loading paged data manual section

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.0/Create-REST-connection/Loading-paged-d…

The actual details of what you precisely need to do will depend on the details of your data source.