topic Re: How to create a loop in SQL Statement in QlikView

How to create a loop in SQL Statement

Tue, 24 Jun 2014 18:26:01 GMT

I have a SQL Load statement in QV which queries a very large table. It's so large that I need to load the days rows by breaking up the SQL into hourly increments. Is there a way to create a looping staement which will allow me to write one SQL statement but loops through the full day?

Re: How to create a loop in SQL Statement

eduardo_sommer — Tue, 24 Jun 2014 19:02:32 GMT

Hi John,

You can create a subroutine with the Load and Select using a where clause that limits the load to one hour. The syntax will be something like

WHERe HourInDB = $(HourInParameter)

This HourInParameter can be a parameter from the subroutine or a control variable in a For Each command.

The structure might be something like:

Sub LoadSub (HourInParameter)

Table:

Load

;

SQL SELECT

FROM DB

WHERE HourInBD = $(HourInParameter);

End Sub

Call LoadSub(1)

Call LoadSub(2)

Call LoadSub(23)

Or a loop for the 24 hours of the day

I hope this have been helpful.

Eduardo

Re: How to create a loop in SQL Statement

maxgro — Tue, 24 Jun 2014 19:31:55 GMT

something like (replace my load from qvd with your sql from database)

for i=1 to 24

table:

load *

from tablemap.qvd (qvd)

where hour = '$(i)';

next i;