topic Loop for SQL statement in QlikView

Loop for SQL statement

Thu, 03 Sep 2009 21:07:28 GMT

Hi everyone,

I've got a script of 15 sql statements. All the statements are for one customer only (see below).

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table1

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table2

.....

SQL SELECT dim1, dim2, dim3 FROM Customer1 Table15

I need to create a loop for all Customers listed in another sql table (over 300 records) and add a dimension for Customer to the script (db name). Could anyone help with the code, please?

BR,

Przemek

Loop for SQL statement

Anonymous — Thu, 03 Sep 2009 22:14:28 GMT

Przemec,
I assume the Customer names are the table names. If this is correct, here is what you can do.
First, you need the number of Customers. It can be defined this way:
LET NumberOfCustomers = NoOfRows('table'); // table is the list of your Customers
Now you can use the loop:
FOR i = 0 to $(NumberOfCustomers)-1
LET C= peek('Customer', $(i), table)
SQL SELECT ... FROM $(C);
NEXT
I didn't test exactly this script, so it may require some adjustments.

Loop for SQL statement

Fri, 04 Sep 2009 00:06:52 GMT

Thank you for the reply, Micheal.

I've tested the script and feel we're almost there. I get Incorrect syntax near 'FROM' error so there must be just a small mistake. Do you have any idea what it could be?

BR,

Przemek

Loop for SQL statement

Anonymous — Fri, 04 Sep 2009 00:09:08 GMT

Take a look at the log file, it can show what is going on.

Loop for SQL statement

Fri, 04 Sep 2009 04:22:41 GMT

Michael,

first Customer goes ok but next ones don't get db.table after FROM and end up with the log (script reduced to 3 loops):

0015 Table1:

0016 SQL SELECT

0017 FROM CDN_Configuration.CDN.Bases

1 fields found: Customers, 183 lines fetched

0021 FOR i=0 TO 2

0022 LET C = peek('Customers',0,Table1)

0023 SQL SELECT Document FROM CDN_Customer1.CDN.VatNag

1 fields found: Document, 1 lines fetched

0024 NEXT

0022 LET C = peek('Customers',1,Table1)

0023 SQL SELECT Document FROM

Error: SQL Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'FROM'.

SQL Scriptline:

SQL State:37000

0024 NEXT

0022 LET C = peek('Customers',2,Table1)

0023 SQL SELECT Document FROM

Error: SQL Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'FROM'.

SQL Scriptline:

SQL State:37000

0024 NEXT

Execution finished.

The wholAny idea what might be the reason?

BR,
Przemek

Loop for SQL statement

Anonymous — Fri, 04 Sep 2009 08:26:11 GMT

It helps, thanks.
Looks like the variable C wasn't defined in the 2nd and 3rd loops. My syntax for peek() function wasn't quite correct. I'm sure that the table name should be in single quotes as well: peek('Customers',$(i),'Table1')
(Without quotes, the table is not properly defined, and peek() takes Customer field from the last loaded table by default. When it was Table1 - it had this field, and you have one correct C. Next time it's a new table, no Customer field - no value for C.)

Loop for SQL statement

Fri, 04 Sep 2009 22:43:59 GMT

Marvelous! It works perfectly 🙂 Just one more thing - I need to load 15 sql tables for all customers. I would like to load Customers table once and only add tablename at each sql statements i.e.

LET NumberOfCustomers = NoOfRows('table');

FOR i = 0 to $(NumberOfCustomers)-1

LET C= peek('Customer', $(i), 'Table1')

SQL SELECT ... FROM $(C)+tablename;

When I added it as above I got an error Incorrect syntax near '+'. Do you know how to fix it?

BR,

Przemek

Loop for SQL statement

Anonymous — Sat, 05 Sep 2009 00:13:52 GMT

Getting there...
You have to change the variable C itself:
...
LET C= peek('Customer', $(i), 'Table1') & tablename
SQL SELECT ... FROM $(C);
...
If it doesn't work - see in the log file if the script creates the correct table name, and adjust if needed.