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.
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!
The Date Time Now Tool is part of the Input Tool Category and it is actually a macro encapsulating other Alteryx tools . To use it, only one selection needs to be made: an output format. That's it, then you can go about your business. You also have the option to output the time with that date.
How do I capture values that are not recognized as dates? I am importing data from an Excel file. Two of the fields are supposed to contain dates, but because the values are entered by hand sometimes they aren't entered in a way that is recognizable by Alteryx as a date. For example, "11/8/`6" is entered instead of "11/8/16". I want to divert the records that could not be read into a separate workflow.
Convert the date field first. If the date does not convert, a NULL is produced. Use a Filter Tool based on finding NULLs to create a separate stream:
See v10.5 workflow attached.
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
Question How do I calculate the number of business days between two dates?
Answer We get this question a lot in Customer Support. It's actually fairly easy to do using a just a few tools.
Assuming your beginning and end date are in two separate fields you can take a look at the sample attached to demonstrate the process.
1) Use the Generate Rows tool to fill in the missing dates
2) Use a Formula tool to create a field with the day of the week for each of the filler dates
3) Use a Filter tool to remove the unwanted days of the week
4) Use the Summarize tool to count the number of days that are left
Note that if you don't already have an unique ID on each record, you'll want to use the Record ID tool to add one to make the summarize process easier.
The sample workflow was built in 10.6, but the same general process can be followed in all versions.
Let's say you have a dataset that gets added to continuously, and records get flagged with dates to indicate when they were added. Now, you want to process only the most recent data from that dataset. How would you pull that?
If you knew that data was always as of yesterday you could calculate that date and then just compare the two.
This formula returns yesterday’s date: datetimeadd(datetimetoday(),-1,'days')
(NOTE: this return the date in datetime format, e.g. 2016-05-25 00:00:00. If your dates are in date format, you will have to make sure that you calculate ‘yesterday’ as a date as well).
But what if the data isn’t always loaded yesterday? What if you have a holiday or the weekend without data loads?
You can create a workflow that uses the summarize tool to find the Max_Date in your dataset and then join back to your data based on that date, as follows (also see the attached workflow):