This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Alteryx Gallery is experiencing a problem in which system emails are not being sent out. As a result, if you are attempting to sign up for a new account, you may be unable to verify your email address. We are working to solve this as soon as possible and will remove this notice once resolved.
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:
where Datebase = a datetime value set to "1899-12-30 00:00:00"
and Field1 is double value = 42397
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?
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.
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:
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.
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.
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!
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.
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.
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).
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.
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:
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!
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!
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!
Often, clients try to format a plain numeric value to a currency format. A simple way to accomplish this is to use the optional "addCommas" parameter in the ToString function. This function converts a numeric value to a string, using a user-specified number of decimal places [numDec]. Though Alteryx doesn’t require the third parameter, if called, the third parameter inserts the appropriate commas (1 or true means format with commas).
ToString([original_value], 2, 1)
Since the new value is a String, simply add in the dollar symbol:
'$'+ToString([original_value], 2, 1)