cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

The Alteryx Gallery is full of interesting and useful Macros which provide 'out of the box' solutions to a lot of use cases! With well over a 1000 macros available which ones do you find most useful?
View full article
  recently wrote to the Alteryx Community asking How-To-Sort-IP-Addresses?  In response to his request, I provided him a macro that not only sorts the data, but also validates that the values are in range {0-255}.{0-255}.{0-255}.{0-255}. The solution post includes the macro as well as having posted the macro to the Alteryx Gallery for all members to access: IP Address Validation   Now I'm now following through with a Knowledge Base article and will review my macro with anyone interested in the following topics: RegEx (Regular Expression) validation of an IP Address Building a Standard Macro Mapping to an incoming field Reverse Field Maps (aka keeping the incoming field name for your IP Address) Conditional processing via a Detour Using a custom image for the Macro Placing a copyright into your workflow/macro Placing a logo into your workflow/macro Finally, I also created a video for those who prefer a more visual explanation     The macro has two (2) containers which isolate the macro interface tools from the IP Validation/Sorting process.  The formula for the IP validation uses RegEx to see if the numbers and dots conform to the standard of:  {0-255}.{0-255}.{0-255}.{0-255}   It would be easy to check for {0-999}.{0-999}.{0-999}.{0-999} with: \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3} This problem required something more creative, like: ^((\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])\.){3}(\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])$   IP Validation 0.0.0.0 to 255.255.255.255 Regular Expression:   ^((\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])\.)({3}\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5])$   Breakdown: ^ Beginning of text ( (\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5]) \.) \d any single Digit [0-9]                                     :: This allows for the group to be 0 through 9 | OR [1-9] the numbers 1 through 9 followed by      :: This allows for 1 to 99 \d any Digit [0-9] | OR 1 the number 1 followed by                             :: This allows for the group 100 to 199 \d{2} any two Digits [0-9] | OR 2 the number 2 followed by                             :: This allows for the group 200 to 249 [0-4] the numbers 0 through 4 followed by \d any Digit [0-9] | OR 25 the number (sequence) 25 followed by      :: This allows for 250 to 255 [0-5] the numbers 0 through 5 followed by \. A decimal point {3} The above logic is repeated for 3 fields (\d|[1-9]\d|1\d{2}|2[0-4]\d|25[0-5]) This logic is similar to the above logic, but doesn’t include a decimal point at the end. $ End of text     Here's a quick peek at the macro: If you don't know what field name the input data will have for a given element that is REQUIRED, you can require field mapping in the macro input.  This will essentially change the incoming field name to your required field.  The issue is that the data is renamed on output to your name.  In order to revert back to the incoming field name, there is an action tool connected both to the incoming macro input as well as to the final select tool.  That's a TIP!   Please watch this short video.  It explains why the TIP is so crucial.     There is a detour that allows for SORTING of the data.  If the detour is checked, the records containing the IP data pass to the right/bottom and gets parsed and sorted otherwise the data flows without interruption.  When sorting the numeric data (remember that this is a text field), version 11 allows you to sort in dictionary order so that you'll get the desired numeric series on output.  That's a TIP!   I invite you to watch the video (less than 15 minutes) and review the macro & word documents.  Please let me know your thoughts and if this article was of value to you.  
View full article
This is a very common error that can occur if the AlteryxService shuts down unexpectedly. Most commonly the error is caused by MongoDB not shutting down properly and the lock file does not get released. This prevents MongoDB from starting the next time you try to start the AlteryxService and returns an error message.
View full article
Question Where can I find available "Big Data Sets" over the internet?    Big data is data that is usually with sizes beyond the ability of commonly used software tools to manage and process within a tolerable elapsed time. A year-long credit card transaction history or CDR (Call data record) of a telecoms company for the last 9 months, behavioral credit data of a large financial institution are some examples... Answer Amazon (AWS) has a Large Data Sets Repository Data.gov has close to 190k public data sets One of the standard datasets for Hadoop is the Enron email dataset comprising emails between Enron employees during the scandal.  It's a great practice dataset for dealing with semi-structured data (file scraping, regexes, parsing, joining, etc.).  It's ~400MB (compressed) and available for download at http://www.cs.cmu.edu/~enron/ Collection of audio features and metadata for a million contemporary popular music tracks  http://labrosa.ee.columbia.edu/millionsong/ .  SecondHandSongs dataset -> cover songs  musiXmatch dataset -> lyrics  Last.fm dataset -> song-level tags and similarity  Taste Profile subset -> user data  thisismyjam-to-MSD mapping -> more user data  tagtraum genre annotations -> genre labels  Top MAGD dataset -> more genre labels  You can either download the entire dataset (280 GB) or a subset of 10,000 songs (1.8) for a quick taste. GDELT set: http://www.gdeltproject.org/data.html NY City taxi data sets 1.1BN records: http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml  Analyzing 1.1 Billion NYC Taxi and Uber Trips, with a Vengeance An open-source exploration of the city's neighborhoods, nightlife, airport traffic, and more, through the lens of publicly available taxi and Uber data Airline data set 1987-2008: https://github.com/h2oai/h2o-2/wiki/Hacking-Airline-DataSet-with-H2O  Google Big Table - hosted by Google: https://cloud.google.com/bigquery/sample-tables Weather, timeline of actions such as pull requests and comments on GitHub repositories with a nested or flat schema, US births 1969-2008, Shakespeare - number of times each word appears, Wikipedia articles over 300,000,000 million rows. The nationality dataset was used to illustrate a blog post related to frequency of births spread by months' year:  http://knowmore.washingtonpost.com/2015/03/31/chart-winter-really-is-baby-making-time/ LENDING CLUB: https://www.lendingclub.com/info/download-data.action Not an actual data set, it is a data set generator that creates simulated call data (CDR) records, if you happen to model telecom behavioural segmentation models, propensity to churn models or mobility etc. http://www.gedis-studio.com/online-call-detail-records-cdr-generator.html  Here is a telecom Italia dataset as a result of a computation over the Call Detail Records (CDRs) generated by the Telecom Italia cellular network over the city of Milano; You may have to sign-in and activate your account but it's totally free... https://dandelion.eu/datagems/SpazioDati/telecom-sms-call-internet-mi/description/  Temporal networks with igraph and R Data Science Centralhttp://www.datasciencecentral.com/profiles/blogs/big-data-sets-available-for-free  KD Nuggets is a well respected analytics blog, they have put together a very nice and deep list: http://www.kdnuggets.com/datasets/index.html  UK Data https://data.gov.uk/data  Google's Public Data Directory: http://www.google.com/publicdata/directory  For the Spatial and GIS folks: http://gisgeography.com/best-free-gis-data-sources-raster-vector/  The mother of big datasets - Reddit. 1.7bn JSON objects; 250GB compressed. https://www.reddit.com/r/datasets/comments/3bxlg7/i_have_every_publicly_available_reddit_comment  Loads of really great links from here as well: https://www.quora.com/Where-can-I-find-large-datasets-open-to-the-public  http://www.imdb.com/interfaces A subset of the IMDb plain text data files is available from their FTP sites as follows: ftp.fu-berlin.de (Germany)  ftp.funet.fi (Finland) Twitter 2010 data set http://an.kaist.ac.kr/traces/WWW2010.html  One of my favorites are Data.gov where there is tons of public data from all sectors, different size sets and in different formats including API connections. This url, http://www.data.gov/open-gov/,  shows each of the local governments in the US. They have varying degrees of completion on the local level.  https://www.linkedin.com/pulse/need-data-bob-wyman?trk=mp-author-card  The Government of Canada has an Open Data portal -- http://open.canada.ca/en/open-data -- it takes some digging to find the gems, but there are some. There's also some open mapping data at -- http://open.canada.ca/en/open-maps.  This 3TB+ dataset comprises the largest released source of GitHub activity to date. It contains a full snapshot of the content of more than 2.8 million open-source GitHub repositories including more than 145 million unique commits, over 2 billion different file paths, and the contents of the latest revision for 163 million files, all of which are searchable with regular expressions. https://cloud.google.com/bigquery/public-data/github   15.49TB of research data available. http://academictorrents.com/  Australia, New South Wales Open data http://data.nsw.gov.au/  USAFacts: Our Nation, in numbers. Federal, state, and local data from over 70 government sources.
View full article
We frequently get questions about how to flag rows in a  data set that are missing values in any column.     In the example below, we will demonstrate how to add a new column to the data that gives a count of null or empty values per row. The end result will give a count of 1 to each of the highlighted rows in the image.  The new column can be used in a filter tool to isolate rows of data that have missing values.
View full article
Alteryx reads .sav files and loads either the numeric representation or the textual representation of values based on the user’s selection. It also reads the variable labels into the Alteryx Field Description.  When writing .sav output, Alteryx will write either the text or the numeric values (depending on what was used in the workflow) as well as the SPSS variable labels which were displayed in the description field. However sometimes to maintain the integrity of the whole SPSS file, clients will want the value labels, value levels, and variable labels to appear in the output file. For these cases, using the Alteryx tools and a few lines of R code (while leveraging the thousands of R packages on CRAN) wrapped in a macro gives us the needed functionality. Attached is a macro which will write the data, variable & value labels back into SPSS.
View full article
This is Part 2 of the Alteryx Gallery Series - The Gallery Series only refers to the Alteryx Server where you can host your Private Alteryx Gallery. This Article with refer to 'Alteryx Gallery' as a privately hosted Gallery on the Alteryx Server.  Part 1 Alteryx Gallery Series - The Lay of the Land 101: Gallery Admin Edition Part 3 Alteryx Gallery Series: App Publishing & Version Control 101 - Alteryx Community  Part 4 Alteryx Gallery Series: How to make the most of your Alteryx Gallery – Use Cases   Each section of the user interface is outlined and described below:   Often utilizing and navigating the Private Alteryx Gallery for the first time (or even for veterans) can be difficult. This article aims to make this process simpler by giving a step-by-step guide on where to find the main areas of the Alteryx Gallery, as well as those hidden features.     Finding the Alteryx Gallery The URL is typically the ServerMachineName(or IP)/Gallery unless another domain has been defined. **** Speak to your IT or Alteryx Server Contact to find URL address. If it is an internal server, the user will need to be logged into your organization’s VPN before accessing the Gallery.   Accessing the Gallery User visits the Gallery on their own: Depending on your pre-defined user authentication setting, the user may be granted access to the Gallery the first time he or she visits the URL. If your Gallery utilizes Windows Authentication, a Gallery Account is automatically created when a new user visits the site. If your Gallery utilizes Built-In Authentication, the new user may create their own Account profile by “Joining” the Gallery from the Gallery home page.   Gallery Admin invites user to the Gallery: The Gallery Administrator may invite users to join the Gallery. This allows the Administrator to set up all of the users specific privileges before that user views the site. If your Gallery utilizes Windows Authentication, the admin would enable “No Access”. You will receive no notification from Gallery once you have registered. If your Gallery utilizes Built-In Authentication, the admin would disable the “Sign-Up Form” (Link to gallery admin page). The user will receive an invitation email and be asked to validate their account.   Lay of the Land: Everyday Gallery User “HOME is where the heart is” When you create a workflow, macro or app in the Alteryx Designer and upload in to the Alteryx Gallery you can CHOOSE to share your workflow to the Public Gallery (Home). You will need to be a Data Artisan to publish to the Alteryx Gallery (Link Permissions page). Users with access to your Gallery URL may access the Public Gallery section. External users cannot view and run workflows in the Public Gallery unless they have been invited by an Admin and have network rights to access the Gallery website. Example Users often upload proof of concepts or demo applications to the Alteryx Gallery which other users can use for reference.   Public Gallery section that have been tagged (after publishing) with a specific keyword set by the Gallery Administrator.   “The last of the thirteen DISTRICTS of Panem” Districts are Alteryx workflows shared in the Public Gallery section that have been tagged (after publishing) with a specific keyword set by the Gallery Administrator.   Example Districts are used for housing macros which are used within different internal departments.     “Welcome to my Private Studio” A user will ONLY receive their own Private Studio if their user permission level (set by the Alteryx Gallery Admin at sign up or before) are designated an Data Artisan User. This will allow them to publish Alteryx workflows from their Desktop Designer. By default, a Data Artisan will only see the workflows they have published themselves in the Private Studio section. To see content created by other Data Artisans, a workflow may be shared in the Public Gallery or via a Collection or Studio.   “Welcome to my Studio”   If you have a Gallery account for your company’s Gallery, you can be invited to a Studio by Data Artisans. This is different from a Private Studio in that you will only have the ability to run applications within the studio you are invited to.       “You’re the chosen one, welcome to my Collection” Collections are a means for ensuring only specific users may access workflows to which they are assigned. Collections are managed by users NOT by the Gallery Administrator. Collections are tied to one Private Studio only. A user must have their own (paid) Private Studio OR be a member of another Private Studio in order to run workflows shared via a Collection. Data Artisans can invite Users to a collection through the users tab inside of the collection. These Users will have to sign up to the Alteryx Gallery or be users in the same domain as the Alteryx Gallery is hosted on. You can have Multiple Admins for collections     “Let’s see these Workflow Results” Gallery Users may view the results/output of their own previous workflow executions. You can never see the results of other user’s executions via the Gallery.       'Let's automate that for you' - Scheduler In Alteryx 11.0 the gallery Admin can allow users to schedule directly from the Alteryx Gallery.  You can click on the scheduler tab and schedule your workflow by choosing the workflow and frequency you wish to run the schedule on. You then get visibility to your schedules only, and have the ability to edit the schedule and remove the schedule from being run.    Best,   Jordan Barker Solutions Consultant 
View full article
This is Part 1 of the Alteryx Gallery Series - The Gallery Series only refers to the Alteryx Server where you can host your Private Alteryx Gallery. This Article with refer to 'Alteryx Gallery' as a privately hosted Gallery on the Alteryx Server.  Part 2 Alteryx Gallery Series - The Lay of the Land 101: User Edition  Part 3 Alteryx Gallery Series: App Publishing & Version Control 101 - Alteryx Community  Part 4 Alteryx Gallery Series: How to make the most of your Alteryx Gallery – Use Cases   The Lay of the Land 101: Gallery Admin Edition   The Gallery Admin is the Godfather of a Alteryx Gallery. The Admin is less involved with the workflow content of the site and more involved with management of users and their overall experience on the Gallery. This article will help the Gallery Admin navigate the Admin settings within the Private Alteryx Gallery.    Each section of the Admin interface is outlined and described below:   System Settings Alteryx Server 10.1  Disable user sign-ups: Limits who can sign up for your Gallery. Forces users to be “invited” to join the Gallery. Allow anyone to run public workflows without singing in: If this option is selected, anyone with access to your Gallery URL may run workflows that are available on the Home Page.      Alteryx Server 10.5 Allow unregistered users to run Public Gallery workflows Default Behaviour for workflow Credentials. Here the Gallery Admin will be able to give the oppourtunity for Data Artisan when uploading apps or end users to enter credentials when they run applications.  This feature is only available in Alteryx 10.5 and was added to increase the Analytic Governance of particular application and data used on the Alteryx Gallery.      Allow users to set options for credentials when saving a workflow The best practice would be to ask users for their credentials so they can only access the data they have privileges for within your internal IT environment.  The interface will look like the screenshot below. When uploading an App from your Alteryx Designer (File>>Save as>>>My Company's Gallery) you can click on 'Workflow Options' & 'Set Workflow Credentials'.    This will give the user three options    'User is not required to specify credentials' - The application/workflow will run as the default Alteryx Server user account (Run As settings in system settings on server config).  'User must specify their own credentials' - When running the application from the Alteryx Gallery the users will have to enter in credentials    'Always run this workflow with these credentials' - The user can enter the credentials at this point, however preset credentials can be added within the Alteryx Gallery Admin section ("Workflow Credentials")   Always run workflows wither server or studio default credentials This will run any workflows with the default Alteryx Server user account (Predefined in the Alteryx Server System Settings) This could be a general account that allows all users to access the data they need to.  This is useful when testing out the Alteryx Server for scheduled workflows   Require users to enter their own credentials when running any workflows 'User must specify their own credentials' - When running the application from the Alteryx Gallery the users will have to enter in credentials          Subscriptions A Subscription is another name for a Studio. A Studio allows Alteryx Designer users to publish and share workflows privately within their organization.  Each user that creates a Gallery account is automatically given their own subscription which can become a Private Studio if the user permission level is designated an Artisan. As a Gallery Admin you will have to make this user an Artisan, take a look at the ‘users’ section below which details how to do this. Each Studio includes a limited number of artisans and members – assigned by the Gallery Admin. Once a subscription has been created, it can be deactivated but not deleted. Gallery Admins are the only ones with permissions to create subscriptions.     Subscription Management Permission levels     Creating and adding users to Private Studios To create a studio (subscription) click on ‘subscription tab’ and click on ‘Add New Subscription’. You will now have the ability to create a studio (subscription) Subscription Screen     This will be the subscription screen below:       Gallery Admin can create studios as well as manage the users and workflows assigned to them. There are numerous options when creating a Subscription or Studio: Private Studio Name: The name for the studio, which displays at the top of the page when the user clicks “Private Studio” on the left menu of the Gallery page. Contact Name: The person who owns and manages the studio. Subscription Type Free: Artisans and Members who are part of the studio can run workflows in the Public Gallery Paid: Artisans who are part of the studio can publish and run workflows in that studio and Members can run workflows Artist Seats: The number of Artisan seats available in the studio. Artisans can publish, run, and share workflows. Member Passes: The number of Member seats available in the studio. Members can run workflows in studios for which they have been assigned. Expiration Date: The date the Paid subscription expires. When a paid subscription expires it is downgraded to a Free subscription. API Enabled: Access to the Private Server Gallery API is enabled by default. The Gallery API supports the ability to authenticate, list apps, run apps, and retrieve app results using an API Key and Secret which are accessible via Gallery Settings.   This is how to add a user to a studio (Subscription). You need to take the subscription key from the ‘subscription’ section (above screenshot) and add this into the studio key within the User profile below.     When a user now publishes from there Alteryx Designer to the Alteryx Gallery they will see the studio name they are associated with. This is linked by the same subscription and studio key. Users can only be part of one Studio at a time.   Gallery Users Gallery Admins can add users directly to the Gallery or they can send an email inviting user to log in and add themselves via the Join now form on the main page of the Gallery. User permissions are managed in the users profile (or in “Permissions” if using Windows Auth) as well as in their assigned studio. The new user is added to the Users page. The users name, email (for Built-In Auth), and name of their studio display in the user box as well as icons that represent their permissions. When using Built-In Authentication, the Gallery Admin can designate the permission level of users before they sign up to Alteryx Gallery on the users tab   Built-in Permissions Screen (Users permission level for the whole Alteryx Gallery)     Windows Auth Permissions screen (Users permission level for the whole Alteryx Gallery)   Gallery Admins can manage user permissions in the Permissions page when using Windows Authentication only. A default permission level (No Access, Viewer, Artisan, Curator) can be set for any new or existing user who joins the Gallery. Admin Tip: Set the default permission to No Access or Viewer if Content Security is of chief concern. This will require users to be invited to run workflows on the Gallery Once a user has been created, it can be deactivated but not deleted. Workflow Credentials This tab within the Gallery Admin section is only available for Alteryx Server Version 10.5 and above This tab will give you the option to pre-define credentials that applications & workflows can be run under. Dependent upon the option you selected within the 'System Settings' tab this will affect this workflow credentials options If you selected 'Allow users to set options for credentials when saving a workflow', this will enable Data Artisans to enter in their credentials when uploading the workflow from the Alteryx Designer.   The best practice would be to ask users for their credentials so they can only access the data they have privileges for within your internal IT environment.        If you selected 'Always run workflows wither server or studio default credentials' this will run any workflows with the default Alteryx Server user account (Predefined in the Alteryx Server System Settings) This could be a general account that allows all users to access the data they need to. This is useful when testing out the Alteryx Server for scheduled workflows   If you selected 'Require users to enter their own credentials when running any workflows' users will have to enter in credentials when they run the workflow on the Alteryx Gallery.  This option could be seen as a best practice if you have a generic collection for multiple different departments and each department has a different permission level to certain tables within a database. Having users define their credentials when running the application in the collection will define the scope of data they can see in line with IT or DBA procedures.  The credentials you add in the 'Workflow Credentials' tab will automatically populate in the dropdown for 'Username' if you ask users to specify their credentials within the Alteryx Designer or when running an application from a Studio or Collection. The only time when this will no appear is if you have the option 'Always run workflows with the server or studio default credentials' set in System Settings.      Jobs This is where you can give users permission to schedule workflows they upload to the Alteryx Gallery. This will allow all artisans to schedule workflows.     Workflows Gallery Admins can add workflows, apps, and macros to the Gallery and manage their icon, description, Run Mode settings, and exemptions. Admins can also create “tags” and associate them to workflows so searches within the Gallery return better results.   Run Mode: Determines the level that workflows are permitted to run in the Gallery. Workflows that contain certain tools or access may need to be blocked. The default Run Mode is selected when the server is configured. Unrestricted: Any workflow can be run. Semi-Safe: Workflows using Run Command, Download, Email, “R”, or Events will be blocked. Safe: Workflows using above tools or accessing/storing data on non-local drives will be blocked. Requires Private Data: Indicates if the workflow requires licensed data to run. Private Data Exemption: An exemption can be given to a workflow with private data so that it can be run.   Notifications The server can send email notifications for various events. The SMTP server information is entered when the server is configured. Gallery Admins can manage the types of notifications and the message text. Admin Tip: Disable notifications so your users don't get spammed by the Gallery.   Districts Gallery Admins can create new districts and modify the district name, description, and icon image. Once the District has been created, it will be displayed below the “Home Page” menu option on the Gallery.   Customize your Alteryx Gallery! Banner Ads can be added to be displayed below the search bar on the Home page. Uploaded Files can be added to the site to which Gallery Users can access via Links. You have the ability to change the Gallery Name, Logo and Color Scheme within the web Browser. This is often used to house FAQs, Help & Links Add link to your Page or URL in the Header or Footers.   If you have any outstanding questions please reach out to our Alteryx Support   Best,   Jordan Barker Solutions Consultant
View full article
  Have you ever been frustrated because Alteryx and your database use different data types to store spatial data and they seem to have a hard time communicating?   If your answer is yes, then it is time to try the Spatial OleDB and ODBC connections!  Below is information for Alteryx versions prior to V 11.     This article will show you what is unique about using spatial connections to connect to your database. For general information on connecting to databases, please refer to the Database Connections page in the Alteryx Help files.   First, you want to select the correct provider. If you are connecting to SQL Server, the SQL Server Native Client as communicates the most seamlessly. This is true for both OleDB and ODBC (as well as non-spatial) connections.     For other databases, select the appropriate provider. If you already have a DSN set up for your ODBC connection, you may use that.        Datatypes Even if the datatype in the Visual Query Builder window does not say “SpatialObj” it will still read correctly as a SpatialObj and be the correct data type in your SQL table. This is what our example table looks like in SQL Server Management Studio:     In Visual Query Builder, the fields look like they might be binary:   The Select tool correctly shows them as SpatialObj:     And they return this map:         BLOB fields at end of SELECT All of the things shown above will work better if you can use OleDB as it is faster and more reliable. However, if you do connect through ODBC, keep in mind that the spatial fields must be at the end of your SELECT statement or you will get this ugly looking error: What to do if your spatial fields are not at the end of your table? No problem – they just need to be at the end of the select statement. Instead of SELECT * FROM SPATIAL_TEST, you will need to use something like SELECT Company, Address, City, State, Zip, Spatial_Field FROM SPATIAL_TEST.     Spatial Objects as Centroids Another feature is to read in spatial objects as centroids:   If you check this option, Alteryx will read in centroids (points) instead of polygons. This does not affect the data stored in the table and you can easily switch back to reading in the polygons by unchecking the box.     Output options There are a couple of options to keep in mind when using a spatial connection to write. First, make sure you are selecting your spatial connection. You can recognize it because it starts with “sdbc”. Another option to help you easily identify the connection string to use is to set up an Alias for your Spatial connection. You can refer to this article in the knowledge base on how to create an Alias.   The Output tool will have a few options not usually available:     Select your Spatial Object Field. NOTE: you can only load or update one spatial field at a time. If your table must contain more than one spatial field, the best way to proceed is to create the table separately (either directly in your SQL Server Manager or through a Pre-SQL Statement in Alteryx), making sure it has a Primary Key and then running updates for the fields through separate output tools.   If your field is a Geometry data type in SQL, you must check the Geometry Spatial Type checkbox, otherwise it will be treated as Geography.   For the difference between geometry and geography data types in SQL Server, see Microsoft’s help here. For a description of spatial datatypes in postgreSQL, see here. For Oracle spatial data types, see here.    You also have the option to select the projection to be used. If you click on the three little dots to the right of the field, you can select from a list of projections:   For more information on projections, THIS is a good point to start.   NOTE: When selecting a projection, make sure it is compatible with your database.    This should help you make the most of your spatial data types both in SQL and Alteryx!  
View full article
One of the great features of the output tool is the option to take the file or table name (or part of it) from a field. It allows you to append a suffix, prepend a prefix, change the entire file name, or the entire file path. It also gives you the option whether to keep the field on output.   One instance where this is especially helpful is if you have a process that runs regularly but you don't want to overwrite the output file every time. You can use the datetimetoday() function (date time functions) to find today's date and then use that to update the filename.    For excel files, things work a little differently because of the filename format as filename.xslx|||SheetName.   The option Change File/Table Name will update the sheet name, not the file name.   In order to update the filename, you have to select the option Change Entire File Path. Upstream, you will have to create a field that contains the entire file path. Here is an example of a full file path that uses today's date as the filename:    "C:\Users\username\Documents" + DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsx|||Sheet1"   See the attached workflow for examples of updating an excel file and a comma delimited file with today's date. 
View full article
The guide covers SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CROSS JOIN, WHERE BETWEEN, WHEREIN, DISTINCT and we hope you find it helpful.
View full article
The 11.0 Tool Sheet is a handy reference document where you can view all the Alteryx tools and Macros included in the 11.0 Tool Palette. The tool sheet is attached to this article.
View full article
Sometimes, data will come with multiple header lines, for example year and quarter might be broken into two rows, like this:      That is not very helpful when you are trying to analyze your data.    You could use a Select Tool and type in new names OR you could do a few transformation and use the Dynamic Rename Tool to merge the rows dynamically, so you are prepared when 2017 rolls around the corner and they start adding new columns to your file!    To fix data that looks like the above, we start with the Sample Tool to split the data into two data streams - one with just the values and one with the header information. Then, we transpose the data to let us fill in the missing years and use the Summarize Tool to concatenate the resulting field values:       Now we can use the header lookup we created as a right input into a Dynamic Rename Tool using the "Take Field Names from Right Input of Rows" option and selecting "Name" as the old field name and Concat Value as the new combined field name.    See the attached v10.5 workflow for details of tool configurations. 
View full article
You know what they say; the devil is in the details. Precious little separates a good report from a great one and it often comes down to just a few details. Don’t let a report header be the detail you left out – use the Report Header Tool to painlessly add header reporting elements to your reporting creations!
View full article
The last reporting element in your reports or presentations, the footer certainly isn’t the least. Add footers without a hitch with the Report Footer Tool, and save the best for last!
View full article
This is most likely caused by a licensing error or an issue that can arise from the AlteryxService shutting down unexpectedly.  It can also be due to an incorrect or expired license, or a correct license not being installed with admin rights, or in the case of Runtime-only users (this type of license has been phased out for all but a few legacy clients).
View full article
One of the three database options when setting up the Alteryx Server is to connect into a User-Managed MongoDB instance.  Why would you want to set up your own implementation of MongoDB?  The main benefits are to take advantage of the features of MongoDB that are not included with our embedded instance.
View full article
One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.
View full article
Most of the Alteryx advanced analytics capabilities - including most of the tools in the Predictive, AB Testing, Time Series, Predictive Grouping, and Prescriptive categories - are built as R-based macros under the hood. If there's a piece of functionality that you're looking for that's lacking in Alteryx but is available in R and you have modest R coding abilities, you can extend Alteryx by creating your own R-based Alteryx tool.   The macro creation process involves four steps (quick links to the guides in the series): Find and install an appropriate R package to provide the needed functionality. Develop an Alteryx workflow that makes use of the relevant R functions via the use of an R tool. This workflow becomes the basis of the macro. Create a macro that provides the basic functionality you want, and test it in a new workflow. Polish the macro by documenting it, giving it the ability to generate a report, and doing other things to make it more polished.  The various Alteryx files created in this tutorial are attached to this post.    Once you've created the new tool, don't forget to share it with the wider community by publishing it to the Alteryx Analytics Gallery.   Background   Recently I have been working with an existing customer that is considering expanding the use of Alteryx within their organization to include other groups. Some of those groups are focused on developing predictive analytics models, and currently its members are using a number of different software products. Based on this, there are certain features that they often use in some of those products that are not available "out of the box" in Alteryx. While these features are heavily used by some members of this group, they aren't as widely used in general. A trade-off we face in developing Alteryx is to provide generally needed functionality without blowing up the number of available tools to where their sheer number becomes overwhelming to new Alteryx users.   In a number of instances we have developed new tools at the request of customers to address their needs, providing them with the tools immediately, and then folding them into a subsequent release of the product or publishing them to the Predictive District on the Alteryx Analytics Gallery. A particular case in point is the MB Affinity tool, which was part of the 10.0 release of Alteryx. The MB Affinity tool provides cosine similarity/distance measures for items. This is a common method used in creating recommendation systems of the "people who bought this item also bought" variety.   Getting back to the issue faced by the predictive analytics team of our current customer, one feature of another product that they currently use, which isn't currently pre-packaged in Alteryx, is a tool that examines the importance of potential numeric predictors for a categorical target field using an entropy based measure known as information gain or Kullback–Leibler divergence. In this series, I illustrate how to create an Alteryx macro that provides this measure.
View full article
Alteryx has recently released (Feb 2017) a new Google Analytics Connector. You can download it here.    Here is an overview of the new GA tool in Alteryx Designer 11.0:     Connecting to Google Analytics is becoming more and more popular and There are a few things you need in order to use the Google Analytics macro: A Google Account (e.g., Gmail) Authorized Access to an existing Google Analytics account   Step 1: Set up a Google Analytics account   Please visit the Google Analytics webpage and sign in https://www.google.com/analytics/     On the landing page for Google Analytics you will need to add the Account Name, Website Name and website URL. Once you have entered this information you can click ‘Get Tracking ID’ and this will generate a Tracking Code for the website you would like to attain information on. Once you have generated this code this will take you to Google Analytics Home Page. Creating the Tracking ID creates a Profile ID and/or View ID with the associated website URLs which are used in the back end in the Google Analytics Macro within Alteryx. If you would like to find this information please click on the Admin tab on the Google Analytics home page and navigate to the ‘Property Settings' and 'View settings' to see the Tracking ID and Profile ID/View ID respectively.       Step 2: Now it is time to set up the Client ID, Client Secret, and Refresh Token needed for the Google Analytics Macro:   Go to the Google developers page: https://developers.google.com/  You will now need to navigate to the 'Google API Console' (This can be found at the bottom of the developers.google.com page).    Once on this page you can click on the 'Analytics API' link      When on the landing page for the Analytics API please press the ‘Enable’ button. Once this is enabled the button should change to ‘Disable’.    Congratulations!  You have registered your application by creating a project.     Step 3: Generate your Client ID and Client Secret Within the API Manager you should see a 'Create credentials' option. Click ‘OAuth Client ID’ when the drop down menu appears.    On the next page make sure the Application type selection is Web application   This will generate additional required information below. You can leave the Name as Web Client 1, but please change the Authorized redirect URIs (second option under restrictions) to: https://developers.google.com/oauthplayground Hit Create and wait a few seconds for Google to create your new project.   Acquire your Refresh Token In another tab in your Web Browser please navigate to Google’s OAuth Playground https://developers.google.com/oauthplayground Once on the landing page firs click the cog icon button near the upper-right corner of your monitor Check the box Use your own OAuth credentials and make sure Access type is set to Offline Paste in your Client ID in the ‘OAuth Client ID’ field Paste in your Client Secret in the ‘OAuth Client Secret’ field Hit Close In the sidebar on the left, scroll down to the Google Analytics API v3 under Step 1 Select & authorize APIs Click the little grey triangle on the left and select https://www.googleapis.com/auth/analytics.readonly Hit Authorize APIs (you will be directed to another page) When prompted, hit Allow. You will be redirected back to the OAuth 2.0 Playground. Once you’ve been redirected back to the OAuth 2.0 Playground hit the Exchange authorization code for tokens button.         Your Refresh Token will be contained in a JSON object towards the bottom of the Request / Response section. You can copy this string and save in the same location as your CLient ID and Client Secret.   Congratulations!! You now have all the pieces you need to use the Alteryx Google Analytics Connector!!   Step 4: Lets move to Alteryx! The Google Analytics macro can be downloaded here and found in the Connectors tab Once you have the macro on the canvas you will have two login options: Online Login: This will take you to your Google Login and will automatically create a Client ID, Secret & Refresh Token Offline Login: This will allow you to enter in your Client ID, Secret and Token. This process is recommended for those scheduling the GA tool.    Once logged in you will be prompted to select an available Account; WebProperties; Profiles. Each configuration window will prompt you to press Next to move to the next screen. The GA tool will allow you to select the Date, Metrics & Goals, Dimensions & Segments and give you a summary view to show you selections Once you see this summary press run and you will now see your data.   Tips and Tricks Check out the S datastream output – it contains summary information with each run, and in this case shows all of the account/property/view combinations that are associated with the credentials and loaded in. The D stream will show the data from Google Analytics. When querying custom metrics, dimensions, or segments, you can only be allowed to select a certain combination. The GA tool will notify you of this. Every query requires you to select a profile, at least one metric, and a date range. Everything else is optional.   Click on Spoiler to see all error messages and troubleshooting tips!   Common Issues (Prior to Version 3)   The Refresh token lasts about 1 hour so please remember to refresh the token as this will prompt errors.   Please repeat Step 4 above to refresh the token. Remember to add your Client ID and Secret into the oauth credentials before Authorizing the API!   ‘The Field ‘id’ is not contained in the record…’ (Upgrade to the latest GA tool) This error message can allude to a number of issues. However, to limit trial and error I have prioritized the solutions below based on prior troubleshooting experience (I know im awesome).    1) Please request 'Full control' or as minimum 'read & write' permissions to the supporting macros folder. For Admin installs this folder can be found in: C:\Program Files\Alteryx\bin\RuntimeData\Macros\Supporting_Macros (Relative to where you installed Alteryx).  For Non-Admin installs this folder can be found in: C:\Users\{USERNAME]\AppData\Local\Alteryx\bin\RuntimeData\Macros\Supporting_Macros (relative to your user name) (App data is a hidden folder so you may need to turn hidden folder on)  The reason is outlined in more detail below, but in short, the GA connector reads & writes files necessary for the API connection in these locations. If we do not have write permissions the API connection will fail and give us the error above.  ****DISCLAIMER - YOU WILL NEED TO GET YOUR IT PERMISSION TO DO THIS. Unless you have admin rights to your machine.  2) Your refresh token has expired Please follow Step 4 above and remember to add your Client Secret and ID into the Oauth credentials section on the right hand side before Authorizing the API.  3)  Are you inside your company firewall? If you have tried the above solutions and neither of them worked please try outside of your company firewall (With your IT departments blessings of course)  I have not seen many instances of this but this did resolve the issue in a few cases.        ‘Tool #349: Tool #4 Error Transferring data: Failure when receiving data from the peer’ (Upgrade to the latest GA tool)   Check Profile ID This can also be firewall issues, check with IT if they are blocking transaction from Google Analytics to the user.     ‘Tool #574: Tool #522: Error creating the file "C:\Program Files|Alteryx\bin\RuntimeData\Macros\Supporting_Macros|GoogleAnalytics.DIMENSIONS.xml": Access is denied.’ (Upgrade to the latest GA tool) ‘Tool #574: Tool #706: Error creating the file "C:\Program Files|Alteryx\bin\RuntimeData\Macros\Supporting_Macros|GoogleAnalytics.PROFILES.xml": Access is denied.’ (Upgrade to the latest GA tool)   The Google Analytics macro is dependent upon deleting and updating four files within Program Files. These four XML files are the Profiles, Dimensions, Segments and Metrics. Currently a command line window flashes for a second at the start of running in Update mode – this is to circumvent an access limitation (because the installation files are located in Program Files, they cannot be overwritten – but they can be deleted and written anew). This allows the metrics, dimensions, and segments XML files to be updated.   Sometimes due to internal setting these files cannot be accessed and you may get an error saying ‘Access Denied’ (error referenced above). If this is true you can contact your IT to give you permissions to this file location. Alternatively you can run Alteryx as Administrator and this may give you the elevated Admin privileges to right to this Location from Alteryx (Right click on the Alteryx Icon and ‘Run as Administrator’.     ‘You have set up the Google Analytics Connector Tool and want to know the # of records is produced in the data output? ‘ (Upgrade to the latest GA tool)   The macro aggregates the data across the entire time period, and is grouped by dimension. If you don’t choose any segments in the query, then there will be one row. If segments are selected, then you’ll get multiple rows back for each possible value of dimensions. If you want to retrieve one record per day, the best way really is to setup a little batch or iterative macro to loop through a collection of dates.     ‘You have got a Client ID, Client Secret and a Refresh Token however you do not return any results. ‘ (Upgrade to the latest GA tool) Make sure you have installed a Google Tracking Code on the target website. If not this is something the web developer would have to do. For more information please look here: https://support.google.com/analytics/answer/1008080?hl=en     ‘Receiving a createRecord: A record was created with no field’s error.’ (Upgrade to the latest GA tool) Try right clicking on the Alteryx Designer Icon and ‘Run as Administrator’. This can give elevated permissions to access the dimensions, Profiles and Metrics in Program Files.     ‘Unknown Variable’ (Upgrade to the latest GA tool)   This error will appear when youe enter in the Client ID, Secret and Refresh Token. Do not fear, just configure the search tab and this error will be removed once you run the GA connector.  Please refer back through the steps above as you have more than likely missed a step in the configuration. This error has appeared when the ‘Analytics API’ has not been enabled.     Could not find file…’ (Upgrade to the latest GA tool)     Please check the ‘Reset to default’ option and run the Google Analytics Connector. This will re-write the four XML file into Program Files.     CONGRATS you have now made it through the worlds longest but most informative Google Analytics Article (in my opinion).   Now go free and play with your Google Analytics Data in Alteryx!      However, if you continue to have problems with the Google Analytics connector please reach out to Alteryx Support     Best,   Jordan Barker Client Service Representative
View full article