Loop function in data load using variable

[sforster]

Hello Everyone: I've been searching  through the forums for a few days and across YouTube but am at a loss. 

Description: I am working with an ODBC connection to a legacy system that has several identical tables for multiple cities.  Let's just say there are census tables, voting tables, weather tables etc that are all identical (in their field names) for each city.  I'm trying to pull one table (lets just say the census table) from all of the different cities in a simple load statement where I can concatenate them together.

Problem:  I keep getting an error when trying to use the looping variable, it's like it's not even being picked up or registered and I'm not sure how to fix it .... unless I need to use a different method all-together?

// this is the simple inline load of the different cities I need to pull
[T1]:
Load * INLINE
[
Cities
CITYxxA
CITYxxB
CITYXXC
];

// this is the simple loop function
FOR i=0 to NoOfRows('T1')-1
let vCities = FieldValue('Cities',$(i))

// this is the one table i want to pull from all cities from the inline load list
[census]:
Load City_Name, City_GEO, Total_POP, M_PRCT, F_PRCT, Unk_PRCT;
SQL SELECT * FROM $(vCities).census;

next;

[Or · Accepted solution]

This is more of a "For each" scenario, such as described here: https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptC...

A regular for...next loop would work here too, of course, but the syntax isn't quite right with the Let statement in multiple places:

* You're missing a semicolon (;) after the let statement (Qlik will highlight the following statement as a syntax error)

* You FieldValue() is trying to get the 0th value in the field Cities, which doesn't exist since values start with 1. You should be using $(i)+1 as the loop is written

let vCities = FieldValue('Cities',$(i)+1);

Generally when working with loops, it's best to activate Debug mode and step one line at a time -after adding the missing semicolon, it was easy to see that vCities wasn't getting populated with a value with the previous code, and from there it was a quick trip to the FieldValue() documentation to check the correct syntax.

[sforster]

Thank you for your help, I don't know I missed this!