Give us Your Best Tips and Tricks - Calling all Tip Meisters for Inspire 2019!

[JessicaS]

Inspire 2019 is just around the corner and we're calling on you to help us with Tips and Tricks for the session. 

We're looking for your "Aha!" moments, your "I cant believe I didn't know you could do that" realizations, and anything you do in Alteryx to save clicks.  What are your go to time savers that the Community could benefit from? Please post in the comments and share your tips with us.

Everyone that posts a tip in this thread will get the Tips and Tricks 2019 Badge!

If we use your tip in the book your username will also be featured. 

We will have special swag for Tip Meisters who are featured in the book that come out to attend our session in Nashville. 

For tip inspiration check out last year's post and book. 

Adding a special thanks to everyone who submitted content in 2018 and came out to the session. 

As you can see from the picture, we had a huge turnout in Anaheim!  We had to add an encore session to make sure everyone that waited in the line got to see the session. 

As always, thanks for your support and we look forward to seeing y'all down in Nashville!!

- The Tips and Tricks Team ( @MargaritaW , @JessicaS  and @HenrietteH )

[cmcclellan]

Does this qualify ?

Memorising the shortcuts from the View menu.  People always seem surprised that I can open/close Overview, Configuration, Results (these are the most important ones for me) so quickly.  Turn them off to see more of the workflow and turn them on to see the information they show.  Combine that with Ctrl-R to run, Ctrl-S to save and Shift-Ctrl-B to add a Browse tool and you're suddenly doing things faster than before.

The Overview window is another great trick to move around a workflow more smoothly (when you're trying to explain the flow) or quickly (when you're trying to jump quickly to a particular spot in the flow).

[MichaelF]

One I often overlook because I use it so often - Mouse Wheel Scrolling on the Tool palette.

On the Tool category list on the Tool Palette, I never use the arrows to navigate to the ones that are hidden (Developer, Laboratory). I actually just position my mouse over the whole list and just scroll wheel! Scrolling up moves right and scrolling down moves left! Less clicks = happier me

[anotherusername]

I'm *constantly* using a sequencing technique like described in the below comment by @MarqueeCrew - and I'm unsure if the lack of threads discussing it is because (a) it's too obvious, (b) people use workflows differently to me, or (c) people don't know about it and use more complex sequencing structures instead:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Block-until-done/m-p/50266/highlight/t...

I use this method with both standard tools and in-DB tools in order to ensure that certain steps occur in the sequence that I need (for example, copying a table into an archive/backup before later over-writing it, or ensuring that a control or log table is (or isn't) updated before the other parts of the workflow have been completed).  I will sometimes use it several times in a workflow to ensure that forked logic is completed before allowing the flow to progress.

It avoids the need to rely on macros or apps or other sequencing methods, and keeps the logic in the same workflow (which can be simpler than having too many nested/sequenced workflows with associated macro path dependencies etc - which can make sharing the workflow more complex).

In essence, it goes like this:

1. If Path 1 needs completing before Path 2 proceeds, then:

2. Create a summarise tool (simple Count or Sum - whatever gets only 1 value returned) at the back end of the Path 1 flow, and

3. Append the Path 1 summarise tool to the start of Path 2 to ensure that it doesn't proceed past that point until Path 1 has completed.

If using the inDB tools, then to perform an "append" equivalent, I use a formula tool to create a new "JoinField"=1 field on both streams, which I then use as the keys for the inner-join, so that the single row of data is appended to each row of the main data that you're dealing with on that path.  (and then drop those dummy and appended summarise fields)

If you need to fully block the reading/loading of the Path 2 input until Path 1 has completed then it's a little trickier.  InDB tools have the added benefit of allowing you to block the Select input query from executing for Path 2 *at all* until the Path 1 summarise is performed (which you can do from the output of the Path 1 Write Data tool, which only exists on the inDB Write Tool) - because the SQL executes in chunks, and it can't issue the SQL query to the server until it has that completed summarised value.  Whereas the standard tools need a bit more fiddling and conniving to ensure that any Path 2 source isn't read until Path 1 is written (because you can't control as easily at what point the workflow will decide to start extracting the data from the input).  But if you just need sequencing of the outputs, then both toolsets work fine.

NB:  If you're using inDB tools then you need to be judicious of writing temp-tables (using the Write Data tool) - ie. ensure that no temp tables exists inbetween the Path 2 input and the Path 1 summarise - otherwise the sequencing may not work (although you should *definitely* use temp tables to improve performance/break up SQL commands when using inDB tools).

Happy to document it a bit more thoroughly (or succinctly?!) if people find it useful!

[danilang]

Here's my "I cant believe I didn't know you could do that" moment that I just recently discovered.

Let's say you have a data set that has 2 columns and you want to CrossTab it to a single row.  Because the Group By section in the CrossTab tool is first, I thought it was mandatory.  Every time I came across this situation I'd use a formula tool to add a dummy RowID field, set to 1 for all the records and group by RowID.  

Imagine my surprise when I found out that this is completely unnecessary.  In the CrossTab, I now just specify the "New Column Headers" field as well as the "Values for New Columns" field and hit run.  DOH!

It's kinda sad when I think that I made it through all the Weekly Challenges without cluing in to that one.

Looking forward to finally meeting some of you at Inspire 2019.

Dan

[patrick_digan]

I have a couple:

1) You can output to %TEMP% if just need a file written to temp space. I use this when I don't want to clutter up my workflow directory by writing a relative file but I do need the file for the moment.

2) use tonumber to change nulls to 0's in a numeric field. If you add 2 numbers and one is null, the result is null. 

ToNumber([Field1])

3) You can add a newline in an expression editor by using quotes and hitting enter. This is useful when working with addresses.

[Field1] +"
"+[Field2]

4) The cross tab can produce a totals row and a totals column! These options require you to scroll down and I never knew they even existed.

[patrick_digan]

And last but not least:

5) When copying a tool that loses its group by settings easily (think crosstab), you can keep it's group by settings if you're careful:

• Click on the cross tab first
• Hold Control
• Click on a second tool (any tool will work).
• Copy the tools (Control + C or Right click on the second tool and click copy)
• Paste the tools (Control + V or Right click on the canvas and click paste)
• Connect the cross tab tool to another stream of similar data.

Since we never clicked on the crosstab tool without data, it's settings stick! The problem with normal copy and paste is that as soon as you paste it, you have the tool selected. Once you select a crosstab tool with no data coming in, it loses it's group by settings.

[CharlieS]

Inbound Pipe Error

That error occurs when a tool upstream has a problem, but it isn't reported. A tool downstream (typically a Union) will report this error and give you no clues how to find the real culprit.

How to find the culprit:

Using a tool container, start where that error occurs and place that tool (and the tool(s) that follow it) in the container and disable it. When you run this, you'll probably still get the inbound pipe error. Move upstream one tool at a time, putting each tool in the disabled container and eventually you'll get a different error at the last tool that was actually causing the problem downstream. 

[Verakso]

My vote goes to #4

And then I am a bit happy that an ACE had it the same way as I did, 'cause I also just saw that recently - the Cross tab is a bit underrated.

Along with the string concatenation you can do in the Summarize tool

/Thomas

[D12monkey]

The use of User Constants when selecting paths that where the Alteryx files will eventually move to.

I.e. files that reside in one server needs to be moved to another server. Using a User constant in the workflow will allow the easier transition of file paths from one location vs having to edit multiple instances via the workflow dependencies menu.

User constants

Some workflows will have multiple files and using the %User.Path% (Path in this instance) as a preamble will allow you to change the location address in one location vs multiple location.