cancel
Showing results for
Did you mean:

# Alteryx Designer Knowledge Base

## How to Create Date from Excel Date Formatted as Number

The Excel number for a modern date is most easily calculated as the number of days since 12/30/1899 on the Gregorian calendar. Attached please find a v10.0 workflow that converts a double (Excel date) to a Datetime in Alteryx.   It uses this function in a Formula Tool: DateTimeAdd([Datebase],[Field1],"days")   where Datebase = a datetime value set to "1899-12-30 00:00:00" and Field1 is double value = 42397

## First of Year and Last of Year

Question The Formula Tool has a built in function to calculate the first day of the month (DateTimeTrim([UserDate],"firstofmonth")) and last day of the month  (DateTimeTrim([UserDate],"lastofmonth"))  of a date. How can I calculate the first and last day of the year of a date? Answer Alteryx outputs the first day of the month as YYYY-MM-01 00:00:00 and the last day of the month as YYYY-MM-31 23:59:59 (substitute 31 for the appropriate last day for given month). In order to replicate that at the year level, we can use the following formulas:   First of Year: DateTimeFormat([TestDate],"%Y-01-01 00:00:00") Last of Year: DateTimeFormat([TestDate],"%Y-12-31 23:59:59") Keep in mind that this formula expects your dates to be in the Alteryx native date format of YYYY-MM-DD HH:MM:SS and have the type of DateTime.

## Date Conversions

How do I get from a date like this...MM/DD/YYYY...to where I have the day integer, the month name, and the year in three different columns?

## How To: Reference Windows User, Paths, or other Environment Variables in a Workflow

How to call Windows User, Paths, or other Environment Variables in a Workflow.

## How To: Create Null Values in Data

How to create null values in a data set.

## How To: Round Numeric Values with the Formula Tool

Whether for mathematical or formatting purposes, you may want to round your numeric data.  Depending on the type of rounding you're looking to do, Alteryx has a solution for you!

## Using Conditional Statements to Change your Data

A large component of data blending is applying mathematical or transformational processes to subsets of your data.  Often, this requires isolating the data that complies with a certain criteria that you’ve set. The Conditional functions build expressions that allow you to apply processes to data that satisfy conditions you set.   #

## Tool Mastery | Formula

Data blending, transformation and cleansing..oh my! Whether you're looking to apply a mathematical formula to your numeric data, perform string operations on your text fields (like removing unwanted characters), or aggregate your spatial data (among many other things!), the Formula Tool is the place to start.  With the examples provided below, you should be on your way to harnessing the many functions of the Formula Tool:

## Modulo (mod) Function: Not just for Evens and Odds

You may have seen the modulo (mod) function used in the awesome Collatz Conjecture macro on the Gallery; here, it's used to determine the parity of the entered integer.  Depending on if the number is even or odd, a different set of operations is applied to the value.

## Expression to Evaluate NULL Conditions

When writing expressions that evaluate NULL conditions, it is a common mistake to write the syntax as: IF [FieldX] = NULL() THEN ...   After all, this is how you would write the expression in SQL.  However, the actual syntax needs to be: IF ISNULL([FieldX]) THEN ....   Keep writing those expressions!

## How To: Remove Leading Zeros From a Field

How do I remove leading zeros from a field?  Use the Formula Tool  a nd the TrimLeft() function to remove leading zeros!

## Floating point numbers are surprisingly strange

Floating point numbers in a computer have some properties that are, unfortunately, surprising to even us engineers used to working with algebra. The problems all come from the limited precision and range. A “floating point” number is represented by a certain number of bits of precision, and a certain number of bits of exponent. In all modern computers the exponent is a power of 2. That means, effectively, that 1 is represented as “1 x 2^0”,  2 is “1 x 2^1”, 0.5 is “1 x 2^-1”, and 0.25 is “1 x 2^-2”. That’s great, but what happens if you want to represent 0.7 in the computer? Ah, well, it does not work out evenly at all. Just as 1/3 comes out as a repeating 0.33333… when represented as a decimal, most numbers do not work out evenly when represented in binary. 0.7 is (1/2 + 1/4 + 1/16 + 1/128 + 1/256 + … ), or as a hexadecimal fraction: 0.B333333… Remember you don’t get an infinite number of bits. If you cut it off after 12 bits, 0.B33, it will be a little bit too low, it will be like 0.699951172, or 4.88 x 10^-5 too small. If you cut it off after 14 bits and you round to the nearest value, you’ll get 0.700012207, or 1.22 x 10^-5 too high. Each extra bit may get you closer, but you will never be exact.   The most common floating point types stored in databases are typically called “float” and “double”, with a variety of different aliases. A “float” is typically a 32-bit quantity, with about 24 bits of precision, and 8 bits for the exponent. If you want to represent 4095.7 as a float, you will use 12 bits for the integer part, and you will have 12 bits left for the fraction part, and you will get 4095.699951172, just a little too low.  On the other hand, if you want to represent 1023.7, you will use 10 bits for the integer part, and you will have 14 bits left for the fraction part, and you'll get 1023.700012207, just a little too high.   A “double” has 53 bits of precision and 11 bits for the exponent. Using double precision decreases the magnitude of the issue, but it never goes away.   The Alteryx Formula Tool does all its operations on numbers as double precision. However, if numbers come in as single precision “float” numbers the Formula Tool cannot magically restore precision to the number. Once precision is lost, it is gone for good. For example, if you give it 4095.7 as a float and subtract 4095.7 as a double, you get -4.88e-05. With 1023.7 as float minus 1023.7 as double you get 1.22e-05.   This issue shows up again when you convert Float fields to strings or to Fixed Decimal formats. Of course, if your number is 4095.699951172, converting it to a string or a fixed decimal with one digit of precision will manage to produce “4095.7”. But what if your number is more like <something>.65 ? Will it round to 0.6 or 0.7? Alas, it depends on how many bits you have left for the fraction. The closest Float to 4095.65 is about 4095.649902, a little low. When rounded to one digit after the decimal point you will get “4095.6”. The closest Float to 1023.65 is about 1023.650024, a little high. When rounded to one digit after the decimal point you will get “1023.7”. That’s a computer for you.     Interested in learning more on data types in general? Check out Alteryx Data Types 101.

## Spatial Functions with the Formula Tool

Alteryx’s spatial functions are not limited to the tools found in the Spatial Palette.  The Formula Tool, for example, has a variety of spatial functions that expand the Designer’s spatial capabilities beyond those in the Spatial Tool Palette.  This article highlights the Formula Tool’s ability to create spatial objects, particularly points and lines, and calculate distance using the built-in expression library.

## Converting strings to numbers

Sometimes, a dataset will contain numbers stored as text. I order to do calculations using those numbers, the datatype will need to be converrted to a numeric data type. If the data is clean, changing the data type in a   select tool   can do the trick. Another option is to use the TONUMBER() function in a   formula tool or   multi field formula tool   (if you have more than one field to convert).

## Converting Values from Scientific (E) Notation

Scientific notation , or E notation , is used to more simply represent values that are very large or very small.  Rather than represent the vertical distance from the top of Mount Everest to the bottom of the Marianas Trench as 19795000 millimeters (why millimeters, you ask?  Well, why not?), expressing this distance in scientific notation, 1.9795e+7 mm, provides a more accessible way to understand the magnitude and precision of that value.  When databases and spreadsheets format data in scientific notation, that formatting may be carried over into Alteryx.  For some users, data in scientific notation can be problematic, especially if the data type is read in Alteryx as a string.  Some Alteryx users have posted their helpful ideas on dealing with converting data in scientific notation to the full numeric value, and the links to those discussion threads are provided below.  This article summarizes and demonstrates their ideas.

## Mapping: Generate Multiple Maps Automatically Using the Grouping Field

The Task I remember vividly many years ago when I was tasked by my former boss at a previous company to create trade area maps for each one of our office supply stores. At the time, we had roughly 1,200 stores. I only had MapInfo to work with and no programming skills to fall back on. Needless to say, I was a little distraught.    It didn't take me long to realize that this feat, as far as I was concerned, could not and would not be completed manually. So, a crash course in MapBasic programming was in order. After a lot of studying, a fair amount of pain and hours upon hours of struggle, I was able to create a MapBasic program that would produce these maps in an automated fashion.   Fast forward 4 years and something amazing happened – I was introduced to Alteryx!   After significantly less studying, pain and struggling, I was able to create a workflow which could to do the same thing, except this time produce three map views for each store. Not only was I able to create the workflow without programming, saving me countless hours, Alteryx produced each map in significantly less time. So, I would run the workflow before leaving for the day and come back in the morning with the nearly 4,000 PDFs waiting for me! We then decided to produce two additional thematics for each store, so now we are talking nearly 12,000 maps. You can only imagine the time savings!   How is this done? The attached workflow (shown below - created in 10.1) is the basis for doing just that. The Grouping function within the Report Map tool (also shown below) is all that is required to create this automation. In this workflow, I want to create a map for each store with each store’s 15 minute drive time and customers. I also want all of the competitors to show up no matter which store is shown. In order to achieve this, I have simply configured the workflow so that the store number is in the Grouping Field for the first three layers previously mentioned, and left the Grouping Field blank for the competitors layer.         Want a separate file for each map? In the Render Tool (illustration below): Make sure to choose a specific output file type in the Output Mode Choose a name and location for the files Check the 'Group Data into Separate Reports' box Select the field to group on (typically 'Group') Choose how you want to modify the file name for each map     As a result, you will see a file created for each map:       Requirements: Each layer must have a common field (Store Number in this case). The Field type of the common field to group by must be the exact same (Int16 in this case) - No mixing of Int16 with Int64 or V_String with String, for instance. Things to Consider: Only one object from each of the layers that you are grouping by will show up on each map (unless you have multiple records with the same ID). So for example, other stores will not show up on the same map, even if they are within the map view. You can add these in as a seperate layer and not group it if you want those within the map view to show up on each map. Zoom to layer – Select appropriate layer to zoom to in each map (drive time boundary in this case). A field called ‘Group’ will flow through the workflow after the Report Map tool, which you can use for map titles, for example.   Please feel free to comment or ask questions, and thanks for reading!

## Boolean versus Positional Results

Historically, most Boolean return values are 0=False and 1=True. Officially, Alteryx uses 0 for False, and -1 for True (yes, that's negative one), with the note that any value other than 0 would indicate the value is True.   The Contains and StartsWith/ EndsWith functions are Boolean, whereas Findstring is positional. This means that when using Findstring, the results are positions, not Boolean values. So, if your field does not contain your search value, it will return a "-1" which means not found, rather than the Boolean "true."   Similarly, filtering using these functions can be misleading. Setting a filter Findstring([Field1], "search_term")=-1 doesn't return records that contain your search term from the True output (contrary to the Boolean -1=True); rather, the True output will comprise the records that do not meet your search criteria.   Conversely, setting a filter Contains([Field1], "search_term")=-1 means the returned records are indeed true; this will return the records that do contain that search term.   Note: The CONTAINS function is case insensitive while the FINDSTRING function is case sensitive. You can make Contains case sensitive with the (optional) third parameter set to 0.   For example: Contains([Field1], 'search_term') is not case sensitive, while the following expression is: Contains([Field1], 'search_term', 0)   The EndsWith/ StartsWith functions are also Boolean. Filtering on EndsWith([Field1], "search_term")=-1 returns the records that end with your search term and are therefore "True."   More examples can be found on the Functions Help pages. Happy Alteryx-ing!