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.
Hope everyone's doing well! I am a newbie at Alteryx and I have had a hard time converting Date/Time data into the desired Alteryx format. In my Excel CSV the time column was in custom date/time format which represents the data as shown in screenshot 1. I input the data into Alteryx, which converted it into 1am, 2pm, 4pm and so on as shown in screenshot 2. The objective in mind is to filter the given data according to certain hours in a particular quarter. I have previously used the formula tool and used the right function to separate the date characters to get to a dataset in a particular month range. But I can't do the same for time because length of characters vary. Could any of you please help me in order to convert this time column into military format? Also could anyone provide me tips on how to further my skills in converting date/time objects into Alteryx Accepted formats in varying cases? Thanking everyone in advance, hope to seek a positive response.
I'm not quite sure why the value in Alteryx is different from the one shown in Excel since its not a Date/Time datatype in Alteryx. A sample workbook & workflow would help shed light on this, but if the Excel values are text & they were brought into Alteryx as shown in your first image, the below suggestion should work. Removing the colon and minutes placeholder (%M) from the below format expression would be needed for the 2nd screenshot's data since the value in Alteryx seems to be modified to lose the minutes.
For source values like screenshot 1, use a DateTime tool from the Parse palette. In the configuration for the incoming format, choose Custom. Then in the format field, enter this expression without the surrounding quotes: "%I:%M %p"
The first percentage symbol + the uppercase i (I) signifies the hour will be in 12-hour format and allows for single or double digit.
The colon (:) is... a literal colon.
The 2nd percentage symbol + the uppercase "M" allows minutes as single or double digit. Alternatively, for your case it looks like minutes are always 0, so you could use two literal zero characters "00" in place of the "%M" to match your incoming data .
The space is... a literal space...
The 3rd percentage symbol + the lowercase "p" designates the location of the am/pm value (uppercase "P" can be used also for this purpose).
While we are on the subject, now if I want to filter this data according to a range I am only being offered the options Null/Not Null. I need to specify a time range to filter however that is not possible. This is how the field looks.
Select the "Custom Filter" option in the Filter tool configuration to enable you to write an expression, then use something like the below to get the results you're after. We need to tell Alteryx to convert the text we're providing (formatted as a time) to a DateTime datatype for comparison with the DateTimeParse function. We'll also need pass a similar format expression to tell the DateTimeParse function how to interpret our comparison time text.
[Order_Hour] <= DateTimeParse("15:00","%H:%M") OR [Order_Hour] >= DateTimeParse("12:00","%H:%M")
Based on the results I can see in your screenshot, it does look like the filter is behaving correctly. I don't know exactly what time range you're trying to keep, but the current filter of "less than 15" or "greater than 12" will match all values. I suspect you're actually wanting values between noon and 3pm, so we need to change the "OR" condition to an "AND" condition.
[Order_Hour] <= DateTimeParse("15:00","%H:%M") AND [Order_Hour] >= DateTimeParse("12:00","%H:%M")
The filter I have used, is of no use. If you recall, with your help I was able to convert values like 1 am, 2 am etc. to 01:00:00, 02:00:00 and so on. I thought I could apply a range filter to get to values between noon and 3pm. However the entire exercise of conversion into the HH:MM:SS format went futile. The custom filter here works fine, but from an efficiency standpoint, it is not the best.