Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ---
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!