Alteryx Designer Desktop Knowledge Base

These methods should work in most versions of Alteryx.    One of the most common issues we see from clients trying to read in a .csv file is that they are receiving an error starting “Too many fields in row x”, and because of this Alteryx can’t read in the file.     There are a couple of different ways you can resolve this error.   First, in the Input Data tool, you can tell Alteryx to treat read errors as warnings to allow the file to be read in.     This will change the Error to a Warning so that Alteryx can read in the file so you can investigate.     The other method is to read the file in with no delimiter. You can do this by changing the delimiter in the Input Data tool to \0.     Once you have your data parsed back out into its fields, you can use the Dynamic Rename tool to correct your field names, a select tool to remove the original field, and a simple Trim() function to remove the extra delimiter from your data.   This process is illustrated in the attached workflow, created in version 10.1.    

Between the RegEx, Text To Columns, and XML Parse Tools, the Alteryx data artisan already has an exceptionally robust selection of tools to help parse uniquely delimited data. However, there are still some data sets so entangled in formatting that it’s labor intensive to parse even for them. Enter the Find and Replace Tool, which captures the ability to find your nightmarish parsing workflows and replace them with sweet color by number pictures. Just kidding, it finds bad jokes and replaces them with good ones. Seriously, though, you could do both if you wanted to because this tool has the capability to look up a table of any number of specified targets to find in your data and will replace them with a table of specified sources. With the help of a few quick configuration steps, this tool can simplify some parsing use cases significantly.

Data Integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct, and it is a fundamental component of any analytic workflow. In Alteryx, creating a macro to compare expected values to actual values in your data is quite simple and provides a quality control check before producing a visual report. Let me show you how to build this.       The two inputs represent the actual and expected values in your data. These data streams are passed through a Record ID tool to keep positional integrity and then passed on to the Transpose tool to create two columns. The first column contains the field names and the second column shows the values within each field. This data is then passed on to a join, matching on Record ID and the Name of the field, in order to compare each value. Lastly, if the data does not match from expected to actual, a custom message will appear in the results messages alerting the user where the mismatch happened within the dataset. The image below shows the error message produced if values differ across datasets.      

** Update ** With the release of 2019.3 the email tool now has the ability to accept email authentication which opens the doors for sending email through Gmail and other web-based email services. Check out this post on how to send email through Gmail using Alteryx.   There have been a couple instances lately where users have wanted to use the Email tool to send email notifications as part of their workflow but run into issues because their email service is web based like Gmail or Hotmail.   Unfortunately the current Alteryx Email tools don’t support email from an online provider.    However, there is still a way to send email from Alteryx with a web based email.  It’ll require the use of the Event tab or the Run Command tool and the installation of a third party tool.  In this example a tool called SendEmail is used (Alteryx does not recommend or endorse this tool, it happened to be free and worked).   From the Events tab or the Run Command tool enter in the command that executes the tool then in the arguments section add in the tags that are required for the particular email tool.   Events Tab:           Run Command tool:         There is another option in this post in the Ideas section by

Is it possible to append to a YXDB instead of having to read in the whole thing, union the new records, and then re-create it? The short answer is, No, but you can accomplish this with SQLITE.

Need to download a file from file transfer protocol (FTP) in a Designer workflow?

Reading XML in Alteryx is a little different than other input types and the reason for this is simple - XML is not a tabular data format and the data structure can vary from one file to the next. To effectively read and parse XML data in Alteryx, you need to use a combination of tools, namely the Input tool and the XML Parse tools.

Tired of pulling conversion rates manually? Me too!   Let’s build ourselves a currency converter in Alteryx Designer version 10.1 using the http://fixer.io/ exchange rates. Fixer.io is a free JSON API for current foreign exchange rates published by the European Central Bank. The rates are updated daily around 3PM CET.   The first thing we will do is gather the API documentation from the Fixer.io website. This will give us the URL we need to make our REST API call. If you are unfamiliar with making REST calls please take a look at Tasha’s

Question Is the Alteryx Help available in languages other than English? Answer Yes! Currently the Alteryx Help file is translated in French, German, and Portuguese (Brazil). For the latest, click here:  http://help.alteryx.com/current/index.htm#HelpLanguages.htm   You will also find the Alteryx Server Quick Start Guide in the same languages.

Use the following instructions to download and complete installation via the Alteryx installation wizard in v11.0!

Create a ‘Random’ sample using In-Database tools

how to reference other cells when creating formulas in excel

Have you ever wanted to enrich your marketing data through improved data blending and analytics. Now is your chance by connecting to Oracle Eloqua through Alteryx!

Some users have reported a problem when importing and exporting macros within the Alteryx Designer when the Regional Settings for the machine are not set to English (United Kingdom) or English (United States) (see screenshots below).

Starting with Alteryx 10.1, a License Owner of an Alteryx license key can manage all the activated seats under that key as long as they were activated on Alteryx 10.1 or later. 

Is it possible to have a question included in a macro that would not show to the user in the configuration window?

Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:   https://www.youtube.com/watch?v=fJjAiAHtCuc   Find more information on our visual query builder below: http://help.alteryx.com/current/index.htm#ChooseTableorSpecifyQuery.htm#Visual    

It's not uncommon to have a situation where you need to conditionally join one dataset to another.  Perhaps the most common is when you want to join one file to another where a date from the first file is between, greater than or less than a date(s) on a second file.  The default tools found in the Join section of the tool palette don't provide a simple way of doing this (it can be done, but you need to string several tools together to make it work.  There is a better way!  Read on...).    There is great macro available in the public Alteryx gallery called Advanced Join (find it here, but spoiler alert... you can download the attached workflow which includes this macro so you don't have to go to the gallery to get it).  The Advanced Join gives you greater latitude than the Join tool.  Most notably, you can select records from file A that are unique to A AND intersect with file B.  Now you may be thinking, “I can do that by unioning the records from an inner join with records from a left join,” and you would be correct.  But it takes two tools to do what one Advance Join does.  More importantly, the Advanced Join allows you to put a conditional statement on your join which is something you can't do with the Join tool.  And it’s this feature - the ability to use conditional statements in a join - which we will focus on for our purpose here.   Let's get into some examples.  I have a file, 'Fruit List’, which contains data about various fruits.  This file contains a Column Id, a Fruit Name, a Start DateTime and an End DateTime:     I have a second file, 'Greek Alphabet’, which contains a Column Id, a Greek letter and a Datetime.       I want to join the two files on ColumnId where the Datetime from Greek Alphabet (file B) is BETWEEN Start Datetime and End Datetime from Fruit List (file A).  Here's the workflow and a screenshot of how to configure the Advanced Join:         And here are what my results look like:       Only one record from Greek Alphabet matched one from Fruit List on ColumnId where Greek Alphabet's Datetime was between Fruit List's Start Datetime and End Datetime.   In the next example, I have the same Fruit List file and want to join it another file, Greek Alphabet that contains just one datetime filed:       The first thing to note is both files have a field called 'DateTime.'  We'll want to give these unique names to avoid ambiguity when we write our conditional state in the Advance Join configuration.              I want to join both files on ColumnId but only when DateTime from Fruit List is LESS THAN DateTime from Greek Alphabet:         And the results...:       Let's look at one last example.  This time, I'm going to use the Fruit List and Greek Alphabet files used in the first example (Fruit List has a Start DateTime and an End DateTime).  I'm interested in matching records where DateTime from Greek Alphabet is BETWEEN Start Datetime and End DateTime from Fruit List.  I'm not matching on ColumnId this time.     For the Advanced Join configuration, I'm going to cross join my files.  (CAUTION: the resulting join could contain as many rows as the product of the number of rows of the incoming datasets - a.k.a. Cartesian join - depending on how restrictive your conditional is.  This means if you're joining 2 datasets that contain a million records each, the resulting dataset could contain as many as one trillion records! ).  If I had wanted to match on ColumnId, I would have had to do that separately using a Join tool. The cross join option only allows you to apply a conditional statement:       Results from our 3rd example:     Notice how 10 records from Greek Alphabet were joined to just one record from Fruit List.     The Advanced Join tool can save you time and a lot of headaches when you want to join files using a conditional statement.  It has some limitations - you can only join two datasets and include one conditional statement per tool, cross join limitation mentioned above - but Advanced Join provides greater capability and flexibility than the standard Join tool.

  On Alteryx version 10.1, if you try to schedule a workflow to to run from its original location on disk and the workflow path starts with a digit, for example C:\1testpath\workflow.yxmd, the workflow will fail to run on the Scheduler. When the job fails, you will see the job at the bottom of the Results view within the Scheduler window and the job date is January 01 0001 12:00:00 AM - this indicates a corrupt job. All other combinations work as expected.          This is a known issue that is being addressed. The workaround is to rename the folder so that the path does not start with a digit, for example C:\a1testpath\. Alternatively, you can schedule the job to run a copy of the workflow stored in the scheduler DB. 

Unable to find the dll: "OCI.dll" Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version. This error may also appear if you do not have the drivers installed. One way to troubleshoot this is to find out what drivers you have installed! You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not havethe correct drivers for 32 bit or 64 bit connection dependent upon which one you chose. Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.

‘Alteryx has stopped working’ - APPCRASH   Scenario: You are running a workflow and all of a sudden Alteryx becomes unresponsive and a windows prompt opens and says ‘Alteryx has stopped working’ with options to close the program. Once you click on problem signature properties it references 'Problem Event Name: APPCRASH'. This error can be due to several factors however there a few troubleshooting steps which have resolved this error for several of our clients in the past.   Are you on the latest version of Alteryx? If possible being on the latest version of Alteryx can help ensure any known bug fixes have been resolved and the software is up to date. To find the latest version of the Designer please click on this link Downloads   Limit the number of Browse tools Every time you add a Browse Tool to the canvas it caches the data locally, therefore if you have a large workflow this can sometimes cause memory issues on your machine (dependent upon your machine environment). This can be adjusted by changing the default sort/join memory, however taking advantage of the Browse Everywhere is highly recommended! Before removing all browses you can also 'Disable All Browse Tools' in the runtime configuration settings to test this theory.        Have you tried the block until done tool? When Alteryx becomes unresponsive it can be due to constraints on the processing power of the machine. More often than not you may receive an error relating to an ‘inbound pipe error’ (also memory related). The Block Until Done Tool (found in the developer category) allows all the pre-processing to occur before passing data downstream (e.g. writing out through an output tool). This often makes it an easier process when writing out to a database.   Adjust your sort/join memory The sort/join memory is one of the most underestimated elements of the designer. Because Alteryx is designed to use all the resources it possibly can, it tries to balance and use as much CPU, memory and disk I/O as possible. There are great tips on how to achieve the right balance here: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/What-is-the-Sort-Join-Memory-Setting/ta-p/1302 Again, as always if this error persists after trying these troubleshooting steps, please do not hesitate to reach out at support@alteryx.com