Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DateTime Format

sparshpankaj
7 - Meteor

Good evening everyone

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.

sparshpankaj_2-1617657035403.pngsparshpankaj_3-1617657054266.png

 

 

 

8 REPLIES 8
MattBSlalom
11 - Bolide

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).

 

You can find all the possibilities in the DateTime Functions help documentation.

sparshpankaj
7 - Meteor

Thank you so much Matt,

My issue has been resolved. Looking forward to learn more 🙂

sparshpankaj
7 - Meteor

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.

sparshpankaj_0-1617663440174.png

 

MattBSlalom
11 - Bolide

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") 

sparshpankaj
7 - Meteor

Thanks for the prompt response, however the following filter is not working. The output is:

sparshpankaj_0-1617675298726.png

I am getting the desired response only when I write the following filter:

sparshpankaj_1-1617675342989.png

This is very mechanical and in no way usable for large data ranges.

Please help :')

MattBSlalom
11 - Bolide

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") 

sparshpankaj
7 - Meteor

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.

Lili1
7 - Meteor

Super helpful!

Labels