topic Re: Using the First function in a Loop in Qlik Automate

Using the First function in a Loop

Marius_ — Thu, 02 Mar 2023 08:42:36 GMT

Please help

I am testing a program that will upload multiple tables. To limit the test I introduce the function First 100 in the loop like:

//Bepaal huidige saldo
Let MyMessage = '# Trek ' & '$(vTable)' & ' - ' & '$(vCountere)' & ' of ' & '$(vCountmax)';
Trace $(MyMessage);

Noconcatenate
First 100
$(vTable)$(vRigting):
Load *
;Sql select *
From $(vTable);

[So this part of the code only extracts all fields from a selected table.]

But this procedure is part of a loop, that will extract data from multiple tables.

The first table's extraction is all clear. But with the next table the following error is given.

[QVX_UNEXPECTED_END_OF_DATA: The operation was canceled]

If I remove the First 100 function, I do not experience the problem.(?)

Why is First 100 function giving me an error on the second table. I can start this loop at any table in my list, and the second table's extraction gives this error.

Re: Using the First function in a Loop

E_Røse — Thu, 02 Mar 2023 10:27:54 GMT

Not exactly what you asked, but it would be better to add your limit to within your sql query, using LIMIT 100 or TOP (100) depending on which SQL dialect you use.

Please like and mark my answer as a solution, if it resolved your issue.

Re: Using the First function in a Loop

Marius_ — Thu, 02 Mar 2023 11:37:12 GMT

Hi Elin. Thanks for your response

I upload 2 tables back to back (With a loop)

I want to limit the tables to 100 rows

My Code use "First 100".

My first query executes and is complete.

The second query returns an error message - See above.

If I REMOVE the "First 100" statements, the the whole code executes completely.

Thus

The first "First 100" statement is causing an error with the second "First 100" code causing the load script to fail.

I am not sure why?

Re: Using the First function in a Loop

E_Røse — Thu, 02 Mar 2023 12:09:11 GMT

I am not sure why either, but if you are loading the data from a database, you would want to add the limit to the SQL query. Otherwise the entire table will be imported first, and the you will keep only the first 100 rows.

Typically you could write

Let MyMessage = '# Trek ' & '$(vTable)' & ' - ' & '$(vCountere)' & ' of ' & '$(vCountmax)'; Trace $(MyMessage); Noconcatenate $(vTable)$(vRigting): Load * ;Sql select * From $(vTable) LIMIT 100;

Let MyMessage = '# Trek ' & '$(vTable)' & ' - ' & '$(vCountere)' & ' of ' & '$(vCountmax)'; Trace $(MyMessage); Noconcatenate $(vTable)$(vRigting): Load * ;Sql select TOP 100 * From $(vTable);

depending on your database.

Please like and mark my answer as a solution, if it resolved your issue.

Re: Using the First function in a Loop

Marius_ — Wed, 08 Mar 2023 12:17:57 GMT

Hi Elin

Thanks for your suggestions, but the SQL code in my load script is not to happy in either suggestion:

[LIMIT 100]

Custom connector error

Connector reply error: SQL_ERROR
Message: "LIMIT" is invalid here (due to grammar).
contains an invalid character or it is a keyword.
(It might be possible to escape it using "!"). Or
a space is missing or there is one space too many

Sql select *

From [table] LIMIT 100

[TOP 100]

Custom connector error

Connector reply error: SQL_ERROR
Message: "FROM" is invalid here (due to grammar).
contains an invalid character or it is a keyword.
(It might be possible to escape it using "!"). Or
a space is missing or there is one space too many

Sql select top 100 *

I think it is an internal Qlikview code error

Re: Using the First function in a Loop

E_Røse — Wed, 08 Mar 2023 12:30:49 GMT

It says Connector reply error: SQL_ERROR, that is not an internal QlikView Error.

You have to use the correct syntax for your source database. That might not be limit or top as I suggested. Did you check the link I provided? So what database are you using?