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

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 w 2918 tIPS LINE.png ho 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 @hhaigh1 )

Tips and Tricks 2018.pdf

Nashville 2019

Contests

Accepted answers

All comments

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/true#M19062

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.

ezgif.com-video-to-gif.gif

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.

User Path 2.PNG

D12monkey

Also adding an email event (under the Events tab) if you are the workflow owner/administrator.

Email event.PNG

This will allow you to get notifications when the workflow is ran based on options you select. I.e want to know when the workflow runs, if it runs with errors, or without errors, et. cetera.

Email event options.PNG

Kenda

I recently discovered that you can make your text bigger within the Configuration Window! Simply click in the expression editor you want to zoom in on, hold the Ctrl button then scroll up on your mouse! You can also make the text smaller by holding Ctrl and zooming out with your mouse.

ezgif.com-video-to-gif (3).gif

The same thing can be done in the SQL Editor!

ezgif.com-video-to-gif (2).gif

ThizViz

When posting solutions in the forums, add screenshots, such as @patrick_digan @D12monkey and @Kenda have done in this post.

It makes it so much easier for people to find solutions without needing to open the packaged workflow to see the backstage magic. :-)

D12monkey

Two more:

1: You can re-name tools allow easy trouble shooting of your workflows.

Do this by selecting the tool, clicking the tag icon and changing the Name and Annotation (as shown in red below).

Changing the annotation will organize your workflows for quick reference.

For example:

Changing the Name will allow you to use the find tool feature (Ctrl +F) and select the exact tool you want to edit:

2: This tool works well in conjunction with the above tip. If you have your tools named you can use this tool via Ctrl+F or via the view menu:

You can sort your tool by Name or by number by selecting the hyperlinked sort by toggle near the X to close.

john_miller9

Here's something that I recently discovered, which I never knew because it's too obvious. When you're reviewing your workflow output in the results section, you sometimes find some interesting data. You can pop the results out into another window while you go off an explore the underlying data. But sometimes, the results set is very large, and you may lose your place in the records you were exploring. In this case, you can pop out the selected records into a new window, rather than all of the records in your results. You also have the option of saving the record subset as it's own table.

Pop Out.png

ThizViz

Here's a troubleshooting tip I learned the hard way (by getting weird results and ultimately working with a solutions engineer).

If you're not getting expected results, especially along the lines of numbers that should be 123 coming out as 122.88759, try adding a data cleanse tool or a formula tool {ToNumber([field1]) or IF IsNull([field]) THEN 0 ELSE [field] ENDIF} toward the beginning of your workflow to convert NULLs to zero. Believe it or not, it can make a difference.

Second troubleshooting tip:

If you're not seeing the number of records you expect, or if data seems to be dropping out of your workflow, turn on Connection Progress in the configuration window. You'll be able to see exactly where the record count drops. If you're still not sure exactly what's going on, add browse and/or summarize tools to the false side of Filter tools and un-used anchors on Join tools. Use the data profiler or group function in the summarize tool to determine what those records have in common (for instance, if you're filtering for ABC, Inc. and these records say ABC, Inc or ABD Incorporated and you didn't know that).

iversen_vasquez

In the Select tool, if you're choosing only a few fields from your dataset, and want to sort the ones you've already selected (or just want to see them close together so that you know which fields you've selected so far), you can click on the tiny box in the upper left corner so that all the fields you've chosen are all together. You can then sort them easier instead of going through all the ones you did not select.

Step 1: Choose desired fieldsStep 2: Click that box in the upper left corner (twice) so that your chosen fields are now all together

Thableaus

Hi!

I posted this one through my LinkedIN profile, maybe it could be useful:

"Cross-Tab tool is a great resource to bring your rows to column headers in Alteryx. But, we have to admit that the process of turning every special character present on the field names to an underscore can be very annoying.

There's an easy way to correct this behavior. Using Unique Tool with the power of Regular Expressions, you are able to setup the Dynamic Rename tool to easily go back to your original headers as they should be. Unique Tool is important to get only a single record per field name. Formula Tool is applied with REGEX_Replace, so we're able to create a lookup field that is similar to what Cross-Tab tool does : all non-word characters become underscores. Finally, the Dynamic Rename Tool allows the user to rename fields based on two fields from a different data stream - the regular name and the underscore name are then compared.

Drop the multiple hardcoded renaming with your Select Tool and simplify!"

JSchiele

Quickly add a Browse Tool to all selected outputs by pressing CTRL+Shift+B!

MarqueeCrew

@JessicaS,

No warnings is a tip. Avoid warnings that you're going to ignore. When configuring a tool like the UNION, don't use the default configuration.

Try to keep your workflow free from warnings and pay attention to the messages after you run. Also, if you use the Manual Config option, if you allow dynamic fields to flow into the union you will get error messages. Just a warning

Cheers,

Mark

PeterS

Right clicking is like a secret universe.

With one or more workflows open, right click on a workflow tab to see different options available. I personally use 'Open Containing Folder' quite a bit.

workflow tab right click.jpg

Once your workflow has completed and has generated a file with the blue hyper-link, left click the link to immediately open the file. Or right click to quickly open the folder the file was created in!

workflow results right click.jpg

DataKyle

Someone probably put this here already, but this helped me with a workflow issue recently:

If you have a workflow that seems to be taking a long time to complete, you can enable workflow performance profiling from Workflow Configuration -> Runtime -> Enable Performance Profiling.

Note: Workflow may take a little longer than normal to complete but the output will include some interesting data to help solve your issue.

Here you can see the time each step took and the percentage of total. In this workflow the primary driver is the initial data pull from Oracle.

estherb47

Tired of counting/guessing how many columns you need for the Text to Columns tool? Let Alteryx figure that out for you!!

By combining Record ID, Text to Columns, Tile and Cross Tab, you can have a more dynamic parsing experience.

Before parsing your data, drop in a Record ID tool to uniquely identify each row. We'll use this field in the Cross Tab tool to rebuild the original table.

Next, add your Text to Columns tool, and type in the delimiters. But, instead of splitting to columns, split to rows instead.

Now we need to build a field to represent which column each row of data should be in. Use the Tile tool, set to Unique Value, with the Record ID as the Unique field. The resulting Tile_SequenceNum field will number the rows within each Record ID, from 1 to n (n being the last one)

Finally, rebuild your table using the Cross Tab tool

If your first row contains headers, use a Dynamic Rename tool to pop that first row into the header row. Otherwise, use a Select tool and rename the columns.

Cheers!

Esther

rag-ryx

Hello Alter-nation people,

I am excited to be at Inspire 2019. I have learned few things during my couple of years using the one and only greatest Data tool - to have ever lived on Planet Earth: Alteryx.

I am not sure if you will call them as tricks but I find them pretty handy, so here we go -

1. In-DB workflow takes too long?

Problem - While working with huge data sources like Spark and creating work flows, sometimes it takes a considerably long time to run the workflow. Even with In-DB tools, especially if you are using multiple tables (multiple Connect In-DB tools), joining them and doing some more manipulation.

Tip 1 - In such cases, try writing a simple SQL query to -

Select only the columns you need
Join the tables in the select query
Do any date/string conversions needed in the query

And use that query in 1 Connect In-DB tool instead of using multiple Connect In-DB tools. No need to write complex queries, a simple query to fetch the data you need is enough.

You will see a considerable decrease in workflow completion time (depending on the overall workflow logic).

Tip 2 - In scenarios where you need to stream your final data set out of In-DB, so that you can use other tools and perform other actions. Try using a Normal Input Tool & write your SQL query in the tool. Sometimes people forget that we don't have to use In-DB tools all the time. An input tool can give the same performance or even better. I have seen better performance in using an Input tool and getting my data compared to using multiple In-DB tools and then streaming the data out.

Things to remember - You will need to write an optimized SQL query to use this tip efficiently.

2. Alteryx hangs and needs to be closed?

Problem - Many times when we have multiple workflows open and data cached and we try to open a new workflow (especially an In-DB workflow) - sometimes designer hangs for a while and if you clicked impatiently you might have to restart it. In such cases you will have to run your workflow again to cache the data and start developing your workflow from that point onward. It can be frustrating.

Tip - Always have an unsaved workflow open in your designer. An unsaved workflow is highlighted by a small asterisk sign after the workflow name.

Now if your designer hangs, just Right Click on the designer logo in Taskbar (at the bottom) and Click on Close Window.

Since you have an unsaved workflow open - the designer will ask you to first save the workflow or not. And voila the designer isn't hung anymore and you don't have to restart it, thus saving time from rerunning everything.

Unsaved workflow.PNG

3. Can't write a SQL query?

Problem - As per my Tip 1, you want to write a SQL query to replace your In-DB workflow. But you don't have much experience in writing queries or your workflow is too complex and you are not sure how to start writing the query.

Tip - Worry not, because Alteryx will do this too for you. Just place a Dynamic Output In-DB tool at the end of your In-DB workflow and it will return you the query, designer generated for your complete workflow. Now you can take this query and edit it as per your need, instead of you having to start writing a query from the scratch. Once done you can use the query in one Connect In-DB tool or Input tool and replace the whole workflow with just one tool. Any changes further would need to be done in the query itself.

Hope this is helpful.

Keep Alter-ing lives.

Best,

Raghav

mbarone

One of my favs . . .

Have several different tools that you want to connect to ONE distinct tool? Say, five Text Input Tools to one Union Tool:

You don't have to manually connect each one. Instead, select all the tools you want to lead into the one tool (I like to use the lasso technique to select them all at once):

Then right click on one of the selected tools and choose "View Possible Connections":

In the window that opens up, all your lassoed tools will be checked already on the left. Simply check the one tool you want to connect them to on the right, and click "Connect":

And just like that, instant multiple connections

Hope to see everyone in Nashville!

Thableaus

This tip is simple but sometimes people forget about the simple features as well.

If you are having trouble with something in Alteryx and you want to quickly ask the Community, you can access the web page through the Help Window.

Cheers,

Greg_Murray

This is a simple one.

Use an open bracket ([) when looking for fields in the formula tool. It will filter the active search to only field values

Claje

This is a bit of an advanced tip. I don't use it often, but when I do it saves me tons of time.

Need to do something in tool configuration that (for whatever reason) the Alteryx UI really doesn't want you to do? (EG: Add a field to a Summarize tool that doesn't yet exist in your data because it is coming from a macro or cross-tab function)

You can edit these in line inside of Alteryx if you have the "XML view" enabled.

Then you can set up everything else about your process in the tool (In my Summarize example, the final output, the operation, separators, etc).

Next, click your fancy new "XML" icon:

Click Edit, and you have an in-line configuration editor, where you can change the name of the field!

Once we click "OK", we'll see that the configuration is now set up, and if I add the field later, it will work exactly how I want.

This will work for every tool on the canvas, although some are easier than others!

Atabarezz

really time saving...

Atabarezz

Sometimes Alteryx in-db (In-database tools) queries get really complicated, and it's not possible to understand what is going on without disecting the SQL in a text editor...

If you haven't seen the inner workings of an In-db query yet one of the previous posts includes that already. Just place a Dynamic Output In-DB tool at the end of your In-DB workflow and it will return you the query.

In order to simplify the query there is a simple trick, place a write-in db and the query will be simplified right after that;

This query will

turn into this smaller one;

Attached the relevant document where I've found out the trick for in-db...

Best

Alteryx In-Database Processing.pdf

philipmannering

Document a running workflow

Here's one I found recently,

If you're running a really long workflow, you might think all you can do is make a cup of coffee and come back in an hour. Instead you could find a tool using Ctrl+f and after selecting a tool / comment box edit the annotations while the workflow is running!

Edit documentation while a workflow is running

Atabarezz

It's not necessarily an "Alteryx tip" because it's not related to the tools but a capability of the "DB" in in-db. And there is no mention of it in help or community either...

If you are connecting to an Oracle database you can get faster results with adding a short hint to your SQL query;

for a query like this

SELECT customers.cust_first_name, customers.cust_last_name, 
  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sales, customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

add /*+ PARALLEL(4) */ and the SQL becomes this;

SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, 
  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sales, customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

You can check out the inner workings here; https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

Atabarezz

There is a similar capability for SQL Server too...

SELECT *
FROM Sales.SalesOrderDetail
OPTION (MAXDOP)

Thableaus

I have another useful tip for people who like to build simple iterative macros.

If you want to manually set up the the number of iterations (or let the user decide by using an Interface Tool), you can tell when your iteration process must stop with a Filter Tool and the Engine Iteration Number:

In this example, I want my macro to iterate 5 times, so when it reaches the 5th iteration (my Iteration Number starts at 0), the True Side will be completely empty (no records will go through, due to the condition being entirely False), and when the Loop Output is empty, the macro stops being iterated.

Cheers,

ThizViz

Wow!

8-O

akshada_r

A trick that I found and helped me immensely while creating/testing workflows is that you can copy the results from the results window and simply click on the canvas and press CTRL+V. Alteryx creates a new Text Input tool filled with the results you just copied. If you "Copy with Headers", the headers become column headers in the text input as well.

acastelazo

If you create a workflow in a new version of Alteryx but need to share it with a user with an old version, you can just open the workflow using an editor like Notepad++ and change the workbook version on the first line. Just make sure you're not using any advanced features not available in older versions...

SGolnik

When pulling from a data source that may not be reliably updated, use the test tool to do data validation and workflow events to send you an email after a run with errors. This will allow you to have peace of mind enough to schedule it instead of manually running it to verify that the data is correct.

email on error.PNG

DanHare

Nice reconciliation pattern - pull out non matching records, then non matching fields within records.

Endlessly fettlable, treating complete no matches differently etc, but very handy to have a test on the ultimate output that triggers when any records are output.

Would love to see other people's takes on this - I'm sure there are more elegant ways of doing this.

Note - just bumped into @MarqueeCrew in the ginormous Gaylord who told me about his new CReW Delta tool for configuring evaluation equivalence of datasets that I'm going to have a look at later on !

RecPattern.yxmd

JohnJPS

An R trick for easily and efficiently applying an aggregate function to every row in a dataset, grouped by a given column, and capturing multiple rows of data generated by said function. This was demonstrated in the answer to this question.

Basic code is here:

library(tseries)
library(data.table)
dt <- setDT(read.Alteryx("#1", mode="data.frame"))
outDT <- dt[, adf.test(r), WidgetID]
write.Alteryx(dt, 1)
write.Alteryx(outDT, 2)

What it does: applies "adf.test(r)" to the dataset, and grouping over "WidgetID".

Just a few lines of easily understood code, and data.table is fast.

And, the output is multiple columns of info provided by the given function:

Cheers,

John

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335