Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

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

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!

12 Replies

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

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

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

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

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

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

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

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.


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

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

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

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

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

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

no data.png

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

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

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

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