topic "Load" within a for loop with passing dynamic values! in App Development

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

Thu, 04 Aug 2016 07:37:12 GMT

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

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

marcus_sommer — Thu, 04 Aug 2016 10:30:28 GMT

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

'$(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

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

Thu, 04 Aug 2016 12:10:19 GMT

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:

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&appid=9d74c2cc68eee64f9109f7e9a6404a1a]

...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!

marcus_sommer — Thu, 04 Aug 2016 13:02:41 GMT

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);

- Marcus

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

Thu, 04 Aug 2016 13:55:02 GMT

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:

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!

marcus_sommer — Thu, 04 Aug 2016 14:06:05 GMT

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

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

Thu, 04 Aug 2016 14:07:48 GMT

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]';

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...

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

Thu, 04 Aug 2016 14:10:07 GMT

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

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

marcus_sommer — Thu, 04 Aug 2016 17:06:03 GMT

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

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

Thu, 04 Aug 2016 19:25:27 GMT

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

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

Thu, 04 Aug 2016 19:30:53 GMT

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