Generating a Sequence of Dates in Alteryx and some Useful Tools

Select:

Select is one of the most useful tools in Alteryx. It allows you to choose which fields you would like to keep for the later segments, it also allows you to rename fields, change data types and add descriptions.

Summarize:

The Summarize tool allow us to do numerous summary processes, such as: grouping, summing, count, spatial object processing, string concatenation, and much more

Browser:

Allows you to have complete views of the underlying data and can be connected anywhere within the Alteryx workflow

‘Generate Rows’:

Creates new rows of data at the record level, and it is useful to create a sequence of transactions, dates or numbers.

Example: Generating a sequence of dates ‘by month’

1. Input your file to the canvas and connect it to the Generate Rows tool
2. Select the option ‘New field’. You can rename as you wish, but making sure that the type is either date or date time depending on the data set.
3. Set the ‘Initialization Expression’ that is your starting date.
4. ‘Condition Expression’. This is where you define the date your range goes up to. So for the first example it would be up until the end date. The first date is the newly created field ‘NewDate’
5. ‘Loop Expression (Usually Increment)’, this tells Alteryx how many new rows we want to create based on the expressions we’ve used above. This formula is saying add 1 month to each date. This will stop when the new date reaches the end date which is specified in the condition expression.6.By adding a browse tool on the end this will show that we now have 138 rows instead of the initial 8 rows,  and for the User ID 1 we now have 50 records.

• If we don’t have an end date, you can use the formula ‘datetimenow()’ in the condition expression section.  However, if we have dates ranging back to a couple of years ago and we use the datetimenow() formula, that will result in lots of records. Nonetheless, if you are joining data sets and you have multiple dates then you can join on the User ID and New Date, removing all the unnecessary dates that were created in the first instance.