cancel
Showing results for
Did you mean:

# Alteryx Knowledge Base

## How to Find the Next Specified Day from a List of Dates

Question If I have a list of dates, how can I find the date of the next Monday for each date? Answer Using a simple workflow, you can calculate the next Monday from any date by using a single Formula tool and configuring as follows: Determine the day of each date Day: DateTimeFormat([Sample Dates],"%a") Calculate the number of days to get to the next Monday based on each day AddDays: Switch([Day],Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1) Add the number of days (from step 2) to get to the next Monday to each date Monday: DateTimeAdd([Sample Dates],[AddDays],"days") Verify that new date is Monday VerifyNewDay: DateTimeFormat([Monday],"%a") You can actually do all of this within one formula, save for verifying the day, if you want to get fancy: Monday: DateTimeAdd([Sample Dates], Switch(DateTimeFormat([Sample Dates],"%a"),Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1),"days")   Things to consider: This workflow assumes that your dates are already in the Alteryx native date format of "YYYY-MM-DD". If they aren't, please visit another Knowledge Base article, Date Conversions, for tips on how to get your dates there!   This was done in 10.1. Sample workflow attached.   Thanks for tuning in!

## Tool Mastery | Report Map

Let's start with the basics of how to create a report map in Alteryx.  To start off, ensure that the layers you want to show in your map have a spatial object field. This can be checked by placing a select tool and confirming that there is a column of type 'SpatialObj.'

## Converting US/English Numbers to the Continental Eurpoean Standard

Alteryx defaults to using the US/English Standard when it comes to number formats. However, for reporting purposes, it is important to remember that not all countries report their numbers in the same fashion. This article shows a quick and easy way to use Raw PCXML to convert numbers in to the Continental European Standard before outputting a final report. Throughout the workflow building process, numbers will be represented in the US/English Standard of 1,000.00.  However, when building an automated report, it is important to remember who the audience will be. In the case of users in countries that use the Continental European Standard, it may best to have Alteryx change the numerical formatting system before outputting the final report. The following example is specific for the Spanish-Spain numbering convention. Process   1. Pass the data through a Table tool to create a Table Report Snippet. 2. Insert a Report Text Tool and format as seen below. The LocaleID is what is specifically driving the formatting change.  For more information on other locale ID's check out this article. 3. Complete your layout and use a Render tool to complete your automated report. Please see the attached workflow for an example in practice.

## Controlling the Order of Operations

Sometimes, especially when interacting with tools/files outside Alteryx, you need to explicitly control the order of tool execution. The classic example is a process whereby you 1) INSERT to a SQL table where an auto-incrementing ID is applied 2) read the records back in order to get the auto-incremented id 3) insert the auto-incremented ID into a different table.   In Alteryx worflow streams that terminate (as in an Output or Render), you can't reliably use Block Until Done to control the stream. One way to reliably control the order of events is with batch macros.   Batch macros guarantee that 1) every iteration is complete before the next iteration begins, and 2) that all iterations are complete before the macro reports itself complete. So if you place a process inside a batch macro and create an output stream from that macro, all processing will be done and the macro still can exist in the middle a workflow stream. You don't even need a Block Until Done as part of the process.   Please see the attached examples

## How To: Add Color Schemes from ColorBrewer2.org to the Report Map Tool

The attached Alteryx Workflow takes the color schemes from www.ColorBrewer2.org and adds them to a new XML file to be utilized as the ReportSettings.xml file installed with Alteryx. You can utilize the new color palettes under the Report Map tools. Note that these palettes will not be added to the Interactive Chart or Insight tools.

## YXI installation - Fix "Exception" error for long path names

How to get rid of annoying "Exception" error during YXI installation. Case of long path names.

## Partner Tools

Looking for more premium connector and tool content to better equip your Alteryx platform for success? Check out what our partners have been up to!

## How To: Remove Leading/Trailing/All Whitespace

There are a couple of different whitespace situations you might get yourself into, but this article has you covered in all of them!

## Troubleshooting Microsoft Access read/write issues

Having trouble reading or writing Microsoft Access files? No worries - Client Services is here to help!

## How To: Replicate the WHERE EXISTS Functionality of SQL

Let's talk about how to replicate the WHERE EXISTS functionality of SQL within Alteryx.   Example 1:  UNION - Add rows from table#1 to table#2 if the key value of table#1 does not exist in table#2.  If desired, the combined data set could be joined with a third data set, but only if the key value in table#3 does not exist with only one key column.     Example 2:  SELECT/UPDATE records from table #1 based on the contents of table #2.  The statement below generates the names of customers who had orders during 2016.   select c1.customer_number ,c1.customer_name from customers c1 where 1 = 1 and exists (select * from customer_orders c2 where 1 = 1 and c1.customer_number = c2.customer_number and c2.order_year = 2016 ) ;     The SQL EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.     The syntax for the EXISTS condition in SQL is:  WHERE EXISTS (subquery)   The  subquery  is a SELECT statement. If the  subquery  returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the  subquery  does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.           Example 1:  Take the unmatched records from the source you are appending data from out of a join between the two data sets and union it back to the data set you are appending to.   Example 2:  Create your "look up list" out of a filter for whatever you set as the condition.  In this example, a year that is in the data.  Join the filtered data back with the other data source. Deselect the data that comes from the filtered source.   Results:     Please see the attached workflow: Where Exists Question.yxmd.

## How To: Use a Cross Tab without having the data rearranged?

Have you ever wanted to do a Cross Tab, but needed the results in a particular order?  You've probably discovered that Cross Tab will dutifully change your data to column headings and keep the rest of the data correlated, but that the output columns will be arranged in alphabetical order.  It's as if there were a Select tool with the "Sort on Field Name" Option thrown into your workflow without you putting it there.

## Output as Text File

Output data as Text File   Within Alteryx there isn't an output option directly to a text file. To achieve this you will need to use a flat ASCII file.   Step 1: Bring in an output data tool and choose the ‘Flat ASCII file (*.flat) option   Step 2: You will then see this below screenshot in the output tool configuration window.     Step 3: You will now need to change the file extension from .flat to .txt, this will chage the flat file to a text format.       Step 4: You can now click on the hyperlink in the results window and open your text file in a supported application.         Example attached.

## Guide to Cross-Validation

In Alteryx, there are 5 customizable options within the Cross-validation screen.