I 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 Parsing
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 Substrings
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.
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.
James Dunkerley's Blog 
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
LikeLike
Great, didnt realise that will make some of my flow a bit simpler
LikeLike