Getting started with Alteryx (part 1) …

imageI have been spending a fair amount of my time at work using Alteryx Designer. It is a nice ETL tool and is a great tool for use along side Tableau. I have found myself using it as a proof of concept for a data flow before transitioning back to my traditional Visual Studio C# world to implement the production code. It allows me to iron out all of the edge cases quickly and then the implementation in C# is very quick. It is possible to use Alteryx itself as a server tool but within the development team we are more familiar with C# and have a fairly rich ecosystem of tools built already.

Anyway I figured I would share some of the things I have found in my use of it. I am still really a beginner and there are a lot of great bloggers out there with a vast array of knowledge and tips and tricks.

Date Parsingimage

One of the first uses I wanted to try was taking in a flat file (generally from a third party), parsing it and then working with the output. Coming from the .Net world I am used to the simple DateTime.Parse methods. I am slightly frustrated by both the DateTimeParse function and the DateTime tool. Neither one allows complete flexibility on input format. It is also frustrating that the two of them do not share the syntax for the format, but that is just my OCD tendencies coming through.

The DateTime tool’s limitation is that it has a fixed list of formats, and of course not the one I need. Although the documentation seems to imply it is linked to the Microsoft parsing functions, but then it has some entries which are not consistent with this. To see if it was just a limitation within the GUI, I tried using a macro to adjust the format argument but that didn’t work (it resulted in a ‘the format is not valid’), would love to make this work but haven’t succeeded yet.

The issue with the DateTimeParse function give most of the functionality I need. I works fine with d/M/yy and yyyy-MM-dd formats. I did find one odd behaviour when working with d-MMM-yy formats in that it needs a leading 0 in the day and year parts (this is not true for a pure numeric format). The table below gives the formulae I used for parsing dates in these formats. One thing to note is the format code %m is for Months and %M is for minutes (the opposite to .Net).

Format Formula
yyyy-MM-dd
yy-MM-dd
DateTimeParse([TextValue],”%y-%m-%d”)
yyyyMMdd
yyMMdd
DateTimeParse([TextValue],”%y%m%d”)
dd/MM/yyyy
dd/MM/yy
d/M/y
DateTimeParse([TextValue],”%d/%m/%y”)
dd-MMM-yyyy
d-MMM-yy
d-MMM-y
DateTimeParse(
IIF(Substring([TextValue],1,1)=”-“,”0”,””)
+REGEX_Replace([TextValue], “-(\d)$”, “-0$1”)
,”d-%b-%y”)

String Conditionals and Substringsimage

Another slight annoyance for me is creating filters based on string conditions. For an end user perspective, I don’t find regular expressions the most friendly (although they are very flexible) and I like the way Excel has ‘Begins With’, ‘Ends With’’ and ‘Contains

These can all be created in a similar way fortunately. Alteryx has a FindString function. Using this you can easily create formulae to reproduce StartsWith, EndsWith and Contains:

Contains FindString([Search],[Target])!=-1
StartsWith FindString([Search],[Target])==0
EndsWith FindString([Search],[Target])==Length([Search])-Length([Target])

One feature I would like is to have support for the SQL like where clause. I think this is quiet easy to do with an expression converting from SQL Like to a Regex but I have not yet had enough time to build this. Will post in Part 2 if I have done it.

One final little thing when working with strings in Alteryx. The Substring function is indexed from 0. As a programmer kind of what I expected but coming from an Excel user perspective not the behaviour I would expect. The FindString function can be fed straight into the SubString function.

In and Not In

In my first attempts I wanted to do create an ‘In’ function. I thought this seemed like a silly thing not to have but then worked out an easy way around it. You can use the Text Input tool to create a simple list and then use the Join tool to create a filter.

IN Flow

I still think a nice GUI allowing you to pick and choose items from a list would be nice but as is so easy to create a filter list it isn’t a big priority.

More to come…

This is just the first couple of things I came across while building up some flows in Alteryx. I will post on parsing CSVs, Macros and my new favorite feature Formula Add Ons next.

2 thoughts on “Getting started with Alteryx (part 1) …

  1. Thanks for sharing your experiences so far. In terms of an “In” function you are able to use “in” in an Alteryx formula.

    Both

    [Field1] in (1, 2, 3)

    and

    [Field1] not in (1, 2, 3)

    work just fine in a custom filter. Agreed in would be nice to have a nice GUI for this in the basic filter and I know this is something we have talked about adding.

    Looking forward to reading your next post.

    Adam

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s