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.
Rather than copying and pasting this process from workflow to workflow, I decided to create a macro (and this macro doesn’t require any configuration!) and make it available to a wider audience with the hope that it will save time and energy by eliminating the need to recreate the process of translating a date week number, quarter, etc.
How To: Combine Date Ranges with a Macro
This module (and embedded batch macro) will provide a comprehensive timeline or date range(s) using multiple, overlapping date ranges.
The macro converts date ranges into spatial objects in order to use the spatial functions in Alteryx to group overlapping or adjacent ranges. This ensures that ranges A and C get are grouped together when A and C do not overlap but both A and C overlap range B (and so forth for larger chains of ranges).
It also allows for “jumping” a user-determined number of days in order to combine regions that do not overlap but are within a specified number of days of one another.
Macros Save Time
One of the best time savings tactics for a developer is to create their own macros for repetitive processes. In the case of this macro, an Alteryx user may need to know what week of the year an event falls on. For example, how many transactions occurred during the 23rd week of the year? Accomplishing this task requires formula tools, time/date fields, and come clever parsing to account for leap years.
The first step is to separate the date into its component parts. By separating the date into its component parts, a calculation can be done later in a formula tool that will create a date for January 1st of the year of that specific record.
The next step is to use a Formula tool to divide the [Month] and [Day] fields by themselves, to create a 1/1 date. This date will be compared against the record date to determine how many weeks it has been since the start of the year.
The final step requires several formulas in one Formula Tool to complete the process. Doing math using the Date formula calculations in a Formula tool requires a specific formatting. The first two formulas determine whether the month is a two digit month or a single digit month. The following two formulas detect whether or not a leading 0 is necessary for proper date formatting and inserts it. The fifth formula brings the components together to create the properly formatted yyyy-01-01 date to compare the record date to. The sixth and final formula determines how many days have passed from January 1st to the record date, divides by 7, and rounds up. The result is the week of the year that the record fell on.
There we have it! Please find the formulas and configurations in the attached workflow.
We get quite a few requests asking how to add the current date to Excel spreadsheet file names. The difference in adding information to your file name in Excel and other output configurations is that you have to use the Reporting tools for Excel to accept the new file name.
Why would I have to use the Reporting tools, you ask? Simply put, Excel thinks that when you are adding a prefix or suffix to the file name that you are stating that additional information is the “Sheet Name” and not appending the file name from the Output Tool. The Render tool in the Reporting tab on your palette basically tricks Excel into thinking that you are creating a separate report and is able to append the date.
In order to add the date to the file name you will connect these tools to your workflow in the order, you see below.
In the Formula Tool, create a new field that is a String type. Go to the DateTime functions and add the DateTimeToday() expression. No configuration of the expression is necessary.
The Table tool will allow you to Group By this new field.
In the GroupBy Configuration, choose the Date field you created in the Formula.
In the Per Column Configuration, uncheck the new field. This will allow you to group by the new field name in the Render Tool and still remove it from the data so that your new field does not appear in your report.
Use the Render Tool to output your Excel spreadsheet with today’s date in the file name.
Output Mode - Choose a Specific Output File.
Output File - Specify the Excel 2007 Spreadsheet (xlsx) and point to where you would like to save the documents. The file name you specify will be replaced with the date in step 5 below.
You will then check the box that says Group Data into Separate Reports.
Field to Group on - You will choose your new field that you created.
Modify Filename By – Replacing Filename With Group.
Report Data – You will choose Table and can leave the rest as defaults.
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.
When using the Salesforce Input tool, you can limit the data to a certain timeframe.Alteryx has the ability to use SOQL date queries so that you pull data for a certain time frame instead of pulling ALL of your data and then having to filter out what you are interested in.
When bringing data into Alteryx a lot of users often add a select tool to check the data type and structure. Data types are very important because of the available operations/functions in tools can be adjusted to fit the data type being used!