Category Archives: Alteryx

Alteryx in the office – episode 2

I had a task the other week where I needed to load Excel data into a datamart. The problem was that the Excel data was not formatted like my destination table in the datamart. So I fired up Alteryx and started playing around.

The Excel sheet was structured with total quantity by week, like so:

ScreenHunter_01 Feb. 25 20.11

(sample data shown)

But I needed it to appear as follows:

ScreenHunter_02 Feb. 27 11.15

The goal is to have total distinct job_name rows equal the QTY value for the corresponding Week of. So 1/3/2011 would have a total of 117 rows.

My result was the following module.

ScreenHunter_03 Feb. 27 11.18

First, I bring in the data with an Input tool. Then I used a Formula tool to create a string field as “jobname”. The next part is where it gets fun. There’s a Generate Rows tool in the Preparation section. I set it up to create a new field, RowCount, initialized at value 1. You can then set the Condition Expression, which for me was RowCount<=[QTY]. The loop iteration is simply RowCount +1 in order to increment rows by 1 each time.

ScreenHunter_04 Feb. 27 11.21

This created rows that match the QTY value for each Week of. Next came the a Record ID tool followed by another Formula tool so that I could create distinct job_names. This is simply a concatenation of the original jobname field with Record ID. I also added a Quantity formula of 1 for quick summation purposes.

ScreenHunter_05 Feb. 27 11.36

A final Select tool was used so I only outputted my desired columns. You can download the sample input, Alteryx module, and sample output files here.

Hope you enjoyed it!

Share Button

Quick Alteryx Win

So I first wrote about Alteryx last year, with the following post, when they invited me to speak at a local joint event with Tableau. If you’re unfamiliar with Alteryx, I suggest hitting the Google because it can make your life very easy.

At my company, we use a lot of MS SQL Server and all that it bundles. This includes SQL Server Integration Services. SSIS is a powerful ETL tool that we leverage to move large amounts of data between systems. But every time I launch Visual Studio and try to make a simple task, I inherently receive error messages that I have no idea what they mean. Yes, SSIS can do a great deal, but it feels like unless you have months and months of training, it is not a friendly and intuitive tool.

My task was to update tables in my SQL datamart that were once stored on a Lotus Notes database but now are in SharePoint as lists. I don’t know why I even opened Visual Studio to attempt this. But needless to say, it quickly frustrated me and then I thought, “Hmm, wonder if Alteryx can connect to SharePoint lists.” Sure enough, it can! I can’t stress how easy it was for me to build a module that would download the data from the each list and push it to my SQL datamart.

So here’s what I did. First, go to the Connectors tools and drag SharePoint List Input tool to your canvas.

ScreenHunter_02 Jan. 15 16.04

Next, complete the Properties for the tool. The SharePoint URL is everything before the /Lists in your url. Input your User Name and Password. Then pick the List you want from the drop down.

ScreenHunter_04 Jan. 15 16.09

I recommend sticking a Browse tool to check you’re correctly downloading what you need.

I then used the Select tool to only capture desired data, rename columns and change their data types. The Select tool is fantastic. It let’s you really narrow down what you need and how you need it. Have you ever done a Transformation task in SSIS? What a pain in the butt.

ScreenHunter_06 Jan. 15 16.23

Finally, each list has an Output tool to my SQL datamart. I really enjoy the flexibility outputting to SQL provides. Notice how you can add Pre and Post Create SQL statements into your module to save you time elsewhere.

ScreenHunter_05 Jan. 15 16.19

I repeated the process above six times, one for each list. Here’s what my module looks like when it was done. Clean and easy!

Alteryx SAP

This was such a great quick win for me, I wanted to share with you all.

Share Button

My First Speaking Engagement

This past week, Alteryx and Tableau hosted a joint event in Bethesda, MD. My Alteryx sales representative called me asking if I would present a use case at the meeting. I was honored and decided to give it a go. This is the first time I’ve been asked to present outside my organization so it was a bit nerve racking. I really wanted to do a great job at telling Discovery’s successes with these tools, with hopes that others too share their experience during the networking happy hour.

If you’re not familiar with Alteryx, you should be. This a great tool that is capable of endless data possibilities. My organization only recently purchased it and it already solved a roadblock I had encountered. This is what my presentation was about. With Alteryx, we were able to join 7 disparate data sources into a single Tableau Data Extract file and create a management dashboard for our Media Operations team. While I won’t go into the entire use case here, a colleague kindly took a few pictures.

My introduction slide into the global reach of Discovery Communications.

presentation3

Discussing one of the final dashboards thanks to our Alteryx and Tableau solution. presentation

Alteryx was kind of enough to post about the event on their blog If you’re able to catch one of the future events, I highly recommend it. 

Like I said, I really enjoyed this experience, even though I was anxious about it all week. I hope to get my Alteryx skills up to where I am with Tableau. I believe we can solve a great deal of data challenges by leveraging both tools in tandem.

I also got to meet a fellow data blogger, which was exciting. Here’s a picture of Emily and me afterwards. Link to her blog.

IMG_20140410_174338

I plan on posting about Alteryx in the future on my data adventures here. Stick around for more!

Share Button