"Load" within a for loop with passing dynamic values!

[#1]

Greetings,

I am experimenting with a weather app and I am trying to pull data for the cities present in my excel file, I am trying something like this:

// Load relatives and friends locations
Relatives:
LOAD
"Family member" as Name,
Relation,
City
FROM
(ooxml, embedded labels, table is Sheet1);

// Load weather data per city
let vCity = '';
let noRows = NoOfRows('Relatives')-1;
for i=0 to $(noRows)
    let vCity=FieldValue('City',$(i));
    LOAD

        $(vCity) as City,
[humidity/value] as humidity,
[temperature/min] as min,
[temperature/max] as max
[location/country] as country
From [http://api.openweathermap.org/data/2.5/forecast/daily?q='$(vCity)';&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a]
Resident Relatives;
next i

The above code doesn’t compile and may sound primitive, but it gives an idea of what I am trying to achieve. I included my private weather API key in case someone wants to test with actual data.

Note that nesting a load statement inside a for loop in Qlik Sense doesn’t work for some reasons!

What I am trying to achieve is map weather data using city as a key to my friends and relatives data set so that I get weather data per city. Not sure if that is clear!

Regards

[#2]

Out put from the debug

Relatives << Sheet1 11 Lines fetched

'Dubai'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Dubai;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Brussels'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Brussels;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Paris'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Paris;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Strasbourg'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Strasbourg;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Berlin'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Berlin;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Napoli'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Napoli;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Majorca'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Majorca;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Casablanca'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Casablanca;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'San Diego'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=San Diego;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

'Rabat'

'[http://api.openweathermap.org/data/2.5/forecast/daily?q=Rabat;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is [WheaterTable]'

WheaterTable << WheaterTable 0 Lines fetched

--- Script Finished ---

[#3 · Accepted solution]

I made it it works, I get the association and I get the weather data per city.

Here is the code for the sake of knowledge sharing and thank you Marcus tremendously for putting me in the right track, code:

// Load relatives and friends locations
Relatives:
LOAD
"Family member" as Name,
Relation,
City
FROM
(ooxml, embedded labels, table is Sheet1);

for i=1 to NoOfRows('Relatives')-1
let vCity=FieldValue('City',$(i));
weatherdata:
LOAD [forecast/time/day] as [time/day],
[forecast/time/clouds/value] as [clouds/value],
[forecast/time/clouds/all] as all,
[forecast/time/clouds/unit] as [clouds/unit],
[forecast/time/humidity/value] as [humidity/value],
[forecast/time/humidity/unit] as [humidity/unit],
[forecast/time/pressure/unit] as [pressure/unit],
[forecast/time/pressure/value] as [pressure/value],
[forecast/time/temperature/day] as [temperature/day],
[forecast/time/temperature/min] as min,
[forecast/time/temperature/max] as max,
[forecast/time/temperature/night] as night,
[forecast/time/temperature/eve] as eve,
[forecast/time/temperature/morn] as morn,
[forecast/time/windSpeed/mps] as mps,
[forecast/time/windSpeed/name] as [windSpeed/name],
[forecast/time/windDirection/deg] as deg,
[forecast/time/windDirection/code] as code,
[forecast/time/windDirection/name] as [windDirection/name],
[forecast/time/symbol/number] as number,
  [forecast/time/symbol/name] as [symbol/name],
[forecast/time/symbol/var] as var,
[sun/rise] as rise,
[sun/set] as set,
[meta/calctime] as calctime,
[location/name] as City,
[location/country] as country,
[location/location/altitude] as altitude,
[location/location/latitude] as latitude,
[location/location/longitude] as longitude,
[location/location/geobase] as geobase,
[location/location/geobaseid] as geobaseid
FROM [http://api.openweathermap.org/data/2.5/forecast/daily?q=$(vCity)&mode=xml&units=metric&cnt=1&appid=9...] (XmlSimple, Table is [weatherdata]);
NEXT

Note that weatherdata can't be changed, the string must remain as for some reason otherwise no data will be pulled from the website!

[#4]