Today's task was to use an API to get data from the World Bank, create a workflow and upload it to snowflake. We than had to create an exploratory, analytical dashboard.

You can find the API documentation here: https://datahelpdesk.worldbank.org/knowledgebase/topics/125589

The most challenging part of the day was getting the desired result from the API call. I never ran into errors on alteryx from making the call, but I found I was only get metadata, or descriptive data back ( for example, the titles of each topic or indicator) rather than the data within the indicators. After most of the day had passed, I was finally able to make the correct call to the API using this URL in a text input tool: http://api.worldbank.org/v2/country/all/indicator/SH.DYN.AIDS?format=json&per_page=70&page=$$$

Creating the rest of the workflow after that was very easy, it was just a combination of parsing out the data and putting it in the correct format. One huge tip I would like to give is after I use text-to-columns to parse the data, I use the select tool to rename the columns as 'Row ID, Header, and Information' so that when I go to transpose, I know straight away which fields I need to select in the relevant place of the configuration.

Another thing to note is that when you have several pages of data come through, each record comes through with it's own number, and you parse on that number when you're parsing with text-to-columns, but as you now have all of the data from all pages, when it gets to the end of the page, the data will start at '0' as the unique ID for the next page and start all over again. For example, I had 69 rows of data come through for each page, so when it got to 69, the row below started again at 0. When you go to transpose this, you cannot use this Row ID as the value to group by, as it will group all the data for 0 together, for 1, for 2, etc and truncate it when you transpose.

To solve this, you need to create a new Row ID, in this case I named it Record ID, and used the following formula in a multi-row formula tool: IF [Row ID] = [Row-1:Row ID] THEN [Row-1:Record ID] ELSE [Row-1:Record ID]+1
ENDIF

You should now be able to transpose on the Record ID.

I then needed to output to Snowflake, to do this I had to install the Snowflake ODBC Driver. I signed up to a free trial on Snowflake, and created a warehouse and a database where my data source would be stored. I found the following post very helpful on how to upload to Snowflake as I have never done it before : https://tinyurl.com/e8p4bze

I really struggled with making the API call today, so I didn't have any time to complete my dashboard. But I learnt a lot in the process, and it was really interesting exploring Snowflake for the first time today.