We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Formula that Recognizes if DataTimeNow is Contained in Data

JacobGFMR
7 - Meteor

Hello,

 

If I have a field that contains a date, like 01/01/2020, but there is also other information in the field, like 01/01/2020 ABC, what formula can I write to obtain only data if the field contains a date, like DateTimeNow,

I am using the DataTimeNow and trying to find data with a current date, but occasionally the field has other information in it as well. 

I need to write a formula to recognize if the DateTimeNow is in that field. 

 

Thanks. 

2 REPLIES 2
TheOC
15 - Aurora

hey @JacobGFMR 

Many ways to do this!
By the sounds of things - the best way might be a find and replace tool.

TheOC_0-1639003562368.png

 


What this allows you to do, is flag records where they contain something specifically. What i've done here is created two streams, one which has data similar to what you mentioned:

TheOC_1-1639003576209.png



And another with a value of 1

TheOC_2-1639003588438.png



I then use a formula to get the date now, and create that as a new field:

TheOC_3-1639003606527.png


Then use find replace to Append the '1' to those records that contain the date field:

TheOC_4-1639003631908.png

TheOC_5-1639003639722.png



Then its a case of filtering where it contains the '1'.


A simpler approach, also attached to the workflow, is a 1 tool example using the filter tool:

TheOC_6-1639003723405.png



You can actually filter on whether it contains specific text - within here you can specify datetimetoday as a string, so if it contains the date in the string, filter those out.

Both approaches are valid, and attached!

Hope this helps,
TheOC

afv2688
16 - Nebula
16 - Nebula

Hello @JacobGFMR ,

 

you can use the regex_match formula for that:

 

REGEX_Match([Field1], '.*\d+/\d+/\d+.*')

 

The formula as I have written it would look for any part of the field that has a date in the format you shared with us 01/01/2021. If the format would be different you would need to change the parameter to be looked at. As it is here, this would find dates that fit the pattern like in these cases:

 

Untitled.png

 

Regards

Labels