Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

12 Replies
marcus_sommer

Try the following by adjusting the path and the missing file-format for this hml/xml file. At first using the wizard to get the correct path and format and then implementing your city-variable within them.

Further there was some small syntax issues - the missing single-quotes around the variable within the load-statement whereby within the path you must leave them. Another correction was on the counter for fields/Rows which started by fieldvalue() and noofrows() by 1.

// 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
for i=1 to NoOfRows('Relatives')
    let vCity=FieldValue('City',$(i));
     WheaterTable:

     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]
next

- Marcus

Not applicable
Author

Hi Marcus,

We are one step closer, thank you so much for spending sometime on my code, I added a few corrections, now this code compiles fine in QlikView at least haven't tried it in Sense:

// 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
for i=0 to NoOfRows('Relatives')-1
let vCity=FieldValue('City',$(i));
WheaterTable:
LOAD
*,
'$(vCity)'
as City
From [http://api.openweathermap.org/data/2.5/forecast/daily?q='$(vCity)';&mode=xml&units=metric&cnt=1&appi...]
next i;

My problem is how can I pass on '$(vCity)' into the URL below:

From [http://api.openweathermap.org/data/2.5/forecast/daily?q='$(vCity)';&mode=xml&units=metric&cnt=1&appi...]


...so I can get data for different cities, and that is part of the app I want to build

Note this link works and generated using QlikView, if you copy paste this you will get data for one London for example:

http://api.openweathermap.org/data/2.5/forecast/daily?q=London;&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a

Thank you

marcus_sommer

Within the path a variable must not have single-quotes around them. An alternatively would be to concat the path within an own variable like:

for ...

     ...

     let vSource = '[http://api.openweathermap.org/data/2.5/forecast/daily?q=' &

               '$(vCity)' &

               ';&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a]';

     trace '$(vSource)';

     load * From $(vSource);

next

- Marcus

Not applicable
Author

This code compiles in QlikView only but not in Qlik Sense (not sure why?) and the it pulls exactly what I was looking for from the web page i.e. the weather data and I see it in the trace , lovely, this the final working version so far:

// 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
for i=1 to NoOfRows('Relatives')-1
     
let vCity=FieldValue('City',$(i));
     
let vSource = '[http://api.openweathermap.org/data/2.5/forecast/daily?q=' & '$(vCity)' &      ';&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is      [weatherdata/forecast/time]';
     
trace '$(vSource)';
      WheaterTable:
     
Load
         
[location/name] as City,
         
[forecast/time/temperature/min] as temp_min,
         
[forecast/time/temperature/max] as temp_max
     
From $(vSource);
next i;


However the data-model for WheaterTable is empty! Relatives table is perfect. See below:

PicForQlik_Community.png

As you see in the data-model I am connecting the two tables on the 'City' key so I can later get weather data per selected city when I place objects/lists on the screen.

Note when I use a static URL for London then the table for WheaterTable is filled with data! Maybe the loop is overriding the WheaterTable table content I guess!

Thank you.


marcus_sommer

For qlik sense you need to create a data-connection which will be called per lib ... for the path. This meant you need always adjust the path by switching script from view to sense and reverse. Again I suggest to use the file-wizard from sense to get the correct sysntax.

- Marcus

Not applicable
Author

I corrected this line

let vSource = '[http://api.openweathermap.org/data/2.5/forecast/daily?q=

' & '$(vCity)' &      ';&mode=xml&units=metric&cnt=1&appid=9d74c2cc68eee64f9109f7e9a6404a1a] (XmlSimple, Table is      [weatherdata/forecast/time]';

To

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

WheaterTable was missing

Also I found that even a static URL doesn't work...

Not applicable
Author

Here is the out put on the screen, I get the cities but not the temperate:

no data.png

marcus_sommer

Have a look on the log-file to see how many the loop runs and how the variables look like. Also a run within the debugger will give these information.

- Marcus

Not applicable
Author

Good evening Marcus,

I did that, I run the script in debug mode and I did some traces, all data looked perfect, exactly what I was looking for but for some odd reasons the WheaterTable table ends up being empty!

I see all the cities pulled from my excel sheet and I see the URL being fed the dynamic values of the cities but data model for some reason is empty for WheaterTable.

I will test a few things and get back to you (this is the fun part of Qlik apps development )

Many thanks