It's so easy to forget how much genius is hidden away in 5 years of Community members solving problems for each other.
I was reminded of this recently when, after posting a fairly convoluted answer to a user's question on calculating last day of month, I saw someone else simply linking in a post from 2016 with the suggestion DateTimeTrim([Date],'lastofmonth'). Here's the link to that post - third comment.
In that instant, I learnt so much, but then again, isn't every day like that? As I was wiping the egg off my face, I felt inspired to go and find more gems like this, and I hope when you all are reading this that you will add in your own gems too from this Community that taught us all how to use Alteryx!
So where do you start when going looking for the best tips, tricks and techniques in almost 20,000 solutions and even more comments? For no specific reason, I typed the word "Genius" into my Alteryx Community search bar. 85 responses! A great start! I don't know about you, but I smile every time I read a post where someone who posted a question, got an answer and then responded with, "@... you're a genius..." I'd like to be a genius one day when I grow up... but in the mean time, I want to learn from all these other geniuses. I also searched the Community for "Amazing" to find some of the amazing solutions posted on the Community.
Here are a few of my favorites from those lists:
Totally Transpose, like totally! When we all edged our mouse pointers over to the Transpose tool that very 1st time, I'd say none of us were thinking, Name and Value. More likely we were thinking of switching rows and columns and then probably felt a little disappointed (I mean no disrespect Transpose Tool). Well, imagine going back in time to that moment and nudging your younger self towards this post! Thank you @JohnJPS
IsNull() or IsEmpty() - which one should I use? If there ever was a joke that started with "3 ACEs walk into a bar..." this post would make me think of it. How many of us really knew that IsEmpty() also checks for nulls? Well, we do now! @BenMoss@jdunkerley79@CharlieS you are all legends!
Remove accents from letters using ConvertToCodePage([fieldname],20127) - This one is a little obscure, I'll admit, but perhaps only because the function is not one we easily recognize. If you ever have 2 data sources that you have to join, but one has non-standard letters (with accents and other kinds of weirdness) while the other doesn't, or worse, it's inconsistent, then this little guy is what you want. Thank you @echuong1
Comparing two data sets is a common question on the Community, which makes this amazing post by @SeanAdams super useful. Not only is it a brilliant macro, but the detailed explanation of how every step works packs the post full of really useful information - a true masterclass!
For me personally, this is one of my favorites - I dig it out every time I'm asked to extract tabular (or other) data from an html structure like a web page downloaded with the Download tool. I stumbled upon it in the first comment of MattD's posts.
As the comment suggested, it looks crazy. When I first saw this, I tried it out and stared at the Regex for a full minute to try and figure out why it works, and then, immediately came to the realization that it's magical and mysterious, and that magic shouldn't be questioned.
It sure makes all (well, most) of those html tags and other gumf just... disappear... magically!
Parse+XML macro in Gallery: This one is not strictly Community (although I'm sure there are plenty references to it in the Community - I've certainly mentioned it a few times), but it's so super useful that I couldn't leave it out. Whenever I need to get under the hood of an XML file, this is the first tool I drag onto the canvas. It flattens a multi-level xml file and extracts all the data to a single column.
While I'm talking about the Alteryx Gallery I'm not sure enough of us tap into the Gallery resources often enough. There are some awesome macros, like the PDF Input tool by @OllieClarke - this is one of the first add-ons you should install to your Alteryx Designer - after the CReW macro pack of course (more on that later).
As with most blogs that you want to be read-worthy, I had spent a good bit of time researching, writing, deleting, reorganizing and sleeping on it. So I had one of my all too infrequent light bulb moments which was to check if someone else hadn't already written exactly the same type of post on the Community. I changed the focus of my Community searching a bit, and came across a post that initially produced one of my all too frequent D'oh! moments. Note to self - next time, do this first! But on closer inspection, it turns out that the focus of said post was significantly different enough not to make me abandon everything I've written so far.
If any of you are anything like me, you would have put off learning about macros for as long as you possibly can, convincing yourself that you're not really missing out and that you can do almost all of what you need without having to get into macros. Then one day, for whatever reason, you take the plunge and immediately start kicking yourself for not doing it much earlier, except of course for that one type of macro that we're all still super scared of... the Iterative macro! which is why I love Hello Iterative macro my old friend by @Treyson so much. After reading this, no one should be scared of iterative macros anymore!
The Inspire Tips & Tricks has also become a reference guide for may over the years. Inspire 2019 edition is shown here. Let's hope there will be an opportunity to publish a new version of this guide not too far into the future.
But I can't go much further without mentioning the undisputed king of the Community, @MarqueeCrew. I was going to start with, "For those who don't know MarqueeCrew...", but sure, everyone knows MarqueeCrew, so he really needs no introduction.
Fast forward 1000 solutions - Mark is the original true legend of the Alteryx Community. Always helpful, and always flaithulach with advice, solutions and videos! Picking the best of Mark's posts is probably the most difficult of this entire blog, so please forgive me if I fail horribly. I'm sure many of you can do way better, so please add your input.
As Mark is the custodian of the CReW macros, that surely has to be first on the list. If you don't know what CReW macros are, go and download them right now - CReW macro pack. There are too many to single out, you're just going to have to play with them all!
Here's the next one I'd like to add, since dates are one of the most common questions on the Community and who better to explain it all ... in this post, Mark explains the use of Substring, Getword and Regex as 3 alternatives to solve the same problem.
This next video is a really easy to understand introduction to Regular Expressions, for those who want to learn Regex but don't know where to start.
I guess it's time to wrap it up. There's no point in me trying to capture everything that's great and awesome about the Community in a single post - that's simply impossible, but I'd love to read about all your favorites in the comments.
I'll leave you with a link to one last post, which incidentally covers an area in Alteryx that is often overlooked, but that I do have a soft spot for - Reporting tools! A question that comes up in the Community quite regularly is how to create merged headers. This can be done by combining a Report Text tool with a Table tool, which @MattD explains really well here, and he also shows how to combine different reporting objects with a Summarize tool! Amazing stuff!
Thank you to all those who work so tirelessly to keep this one of the greatest support Communities out there!