Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

loop through stored procedure with multiple nested loops

Stored procedure results in 1 row, how do I get all results for this table? with a loop for each input?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Well, that should certainly be possible. But performance may not be all that good. I don't know the particulars of odbc/oledb, but calling an sp for every record is sure to have quite a bit more overhead than loading records from a single sql select request.

The loop for the dates is a straightforward

LET startdate = num(date#('01/01/2013','DD/MM/YYYY'));

LET enddate = num(today());

for date = startdate to enddate

      // do stuff

next

For the cluster and location it depends. If the number of values is small you could use string arrays

SET vClusters = "'Cluster1','Cluster2','Cluster3'";

for each cluster in $(vClusters)

     // do stuff using '$(cluster)' for the current cluster value

next

If the clusters and locations are already loaded in tables you could use the fieldvaluecount function for the field that contains the cluster values (same for the locations field).

for i = 1 to fieldvaluecount('ClusterName')

     LET cluster = fieldvalue('ClusterName', $(i));

     // do stuff using '$(cluster)' for the current cluster value

next


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

Either that or rewrite the stored procedure or wrap the stored procedure in another stored procedure that goes loopy on the database server.


talk is cheap, supply exceeds demand
Not applicable
Author

well i have no db access, so the idea was to use a loop like

loop value 1 (startdate - enddate)

     loop value 2 (cluster)

          loop value 3 (location)

load from SP for each day

          end loop 3

     end loop 2

end loop 1

any examples online or personal like these? (SP sends 1 resultrow with 5 columns)

Gysbert_Wassenaar

Well, that should certainly be possible. But performance may not be all that good. I don't know the particulars of odbc/oledb, but calling an sp for every record is sure to have quite a bit more overhead than loading records from a single sql select request.

The loop for the dates is a straightforward

LET startdate = num(date#('01/01/2013','DD/MM/YYYY'));

LET enddate = num(today());

for date = startdate to enddate

      // do stuff

next

For the cluster and location it depends. If the number of values is small you could use string arrays

SET vClusters = "'Cluster1','Cluster2','Cluster3'";

for each cluster in $(vClusters)

     // do stuff using '$(cluster)' for the current cluster value

next

If the clusters and locations are already loaded in tables you could use the fieldvaluecount function for the field that contains the cluster values (same for the locations field).

for i = 1 to fieldvaluecount('ClusterName')

     LET cluster = fieldvalue('ClusterName', $(i));

     // do stuff using '$(cluster)' for the current cluster value

next


talk is cheap, supply exceeds demand