RW API Web Data Connector for Tableau - by ACAPS

[Miguel Hernandez]

Hello everyone,

Are you using Tableau software? If yes, this post can be useful for you! 

Our colleagues at ACAPS have developed a connector between ReliefWeb and Tableau through ReliefWeb API. It allows to get the data about the disasters, reports and jobs posted in ReliefWeb since the beginning of times (well, since there is data in ReliefWeb, which is 1996).

For each of the content types you can choose what countries are dates are you interested in for your analysis.

After that, you can use the dimensions (or facets) provided by the connector to analyze ReliefWeb data through themes, affected vulnerable groups or any other dimension.

At the moment, the connector is hosted at http://pabloibz.com/DEV/WDC/RW/

Also, you can download the source code from https://github.com/reliefweb/acaps-rw-tableau and install the connector in your own server (or even a laptop with WAMPP/XAMPP).  It is very easy to set up a local server. For doing so, just install the software http://www.wampserver.com/en/. There are many tutorials on Internet. After you install WAMP/XAMPP, just copy the files into /htdocs/acaps-rw-tableau and the connector will be available at http://localhost/acaps-rw-tableau

For using it with Tableau, follow this instructions for Windows http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.html#examples_web_data_connector.html and this for Mac https://onlinehelp.tableau.com/current/pro/online/mac/en-us/examples_web_data_connector.html

If you find it useful, please share with us!

Regards :)

Miguel Hernandez

ReliefWeb Product Manager

[pablo.d...@gmail.com]

Hello,

I have tried using the web connector ( http://pabloibz.com/DEV/WDC/RW/jobs.html ), and Tableau shows me the fields, but not the data. Is the URL still valid ?

Best,
Pablo

[Miguel Hernandez]

Hi Pablo,

It seems that the page is not hosted there anymore.

If you want to use this connector, I would recommend that you install your own server with WAMPP (for Windows) or MAMP (for Mac). The installation has a nice wizard where you don't have to modify any option and when done you only have to download the ZIP of the project on Github and extract the files at  /htdocs/acaps-rw-tableau of your XAMP installation directory.

Let us know if that works for you.

Best,

[pablo.d...@gmail.com]

Hi Miguel,

Thank you. I tried with the local server, but the result is not better. I can install WAMP and the github files. But the query does not go anywhere. It just runs on empty and Tableau does not connect.

Is there a simple way to retrieve any of this data as table ?

Best,
Pablo

[Miguel Hernandez]

When I need to use RW API data as a table, I use this tool for Google Sheets: https://github.com/bradjasper/ImportJSON (instructions on the link).

For example, to retrieve jobs by moths from Dec 2014 to March 2017, use:

=ImportJSON("https://api.reliefweb.int/v1/jobs?appname=rw-monthly-stats&filter[operator]=AND&&facets[0][field]=date.created&facets[0][interval]=month&facets[0][sort]=value:desc&filter[conditions][0][field]=date.created&filter[conditions][0][value][from]=2014-12-01T00:00:00%2B00:00&filter[conditions][0][value][to]=2018-03-31T23:59:59%2B00:00&limit=0&preset=analysis", "/embedded/facets/date.created/data", "")

Here, we are using the date facet, you can change it to countries if you want to get the number of jobs per country. Please, also be aware that the query is filtering by dates too.

To retrieve the number of jobs published on March'18 you can use:

=ImportJSON("http://api.reliefweb.int/v1/jobs?appname=rw-monthly-stats&filter[operator]=AND&filter[conditions][0][field]=date.created&filter[conditions][0][value][from]=2018-03-01T00:00:00%2B00:00&filter[conditions][0][value][to]=2018-03-31T23:59:59%2B00:00&preset=analysis", "/totalCount", " ")

Also, be aware that the RW API only returns 1,000 results on each call. If you need more results you should use the offset parameter on your call. 

If you need more than 1,000 what you can do is to set up the query with the ImportJSON in Google Sheets, copy the results of the 1,000 retrieved results into another sheet and modify the offset to get the next 1,000 results.

Hope this helps.

[pablo.d...@gmail.com]

Hello Miguel,

Thank you for your support. It took me a while, but I was able to run your formula in the Google sheets. But it is unfortunately not what I was looking for. I do not need aggregated stats through the API, but the raw data with the essential information for me: Location, Organizations, Start date (and if not too difficult the other fields). So I can use the raw data, for pivot tables, stats and maps.

Is there an easy way to get this?

All the best,
Pablo

[Miguel Hernandez]

Hi Pablo,

Once you have this running, you only have to use now the proper API call to obtain the results you need. 

Use this query to get a list of jobs with source, country and created date fields getting the last published ones first:

https://api.reliefweb.int/v1/jobs?appname=rwapi-group&fields[include][]=country.name&fields[include][]=source.name&fields[include][]=date.created

&sort[]=date.created:asc

&preset=analysis&limit=1000&offset=0

With this, you will get 1000 results. So, what I was suggesting was to get these results, copy them in another sheet, modify the offset (+1000) and run the query again. Repeat the process as many times as you need.

If using this process, I would recommend that you select all the fields you need thoroughly before starting, as it can take some time. Also, take a look at the dates as you can use posting date or closing date. The list of fields can be found in the API documentation, Fields table section, jobs tab.

Let me know if that helps.

[pablo.d...@gmail.com]

Perfect ! thank you :-)

[Miguel Hernandez]

And please, share the results of our investigations :) 

It seems really interesting!

[pablo.d...@gmail.com]

I will. Thank you again :-)