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 Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

Date comparision

AmruthaPydeti
8 - Asteroid

Hi There,

I am new to Alteryx. Please see below data and help me how to clean up my data

 

Date                 FieldA      DateFirst

NULL                NULL      04-01-2018

NULL                NUL        05-01-2018

NULL                NULL      06-01-2018

NULL                NUL        07-01-2018

NULL                NULL      08-01-2018

NULL                NUL        09-01-2018

10-03-2018       10           10-01-2018

11-09-2018        20           11-01-2018

03-01-2019        30           03-01-2019

NULL                NULL       04-01-2019

NULL                NULL       05-01-2019

 

So Now my output should look something like this

 

10-03-2018       10           10-01-2018

11-09-2018        20           11-01-2018

03-01-2019        30           03-01-2019

NULL                NULL       04-01-2019

NULL                NULL       05-01-2019

 

I need to get minimum of Date column and filter to the condition [DateFirst]>= ToDate(DateTimeTrim([Minimum of Date],"month")).

 

 

3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus

Your first step should be to convert your data to the standard Alteryx-friendly datetime format (yyyy-mm-dd) - makes working with dates so much easier for the rest of your workflow! You can very easily do this with a DateTime tool for each of your date fields that needs to be standardized. 

 

Once you have your standardized dates, filter for records that are not null in the [Date] field, and then use a Summarize tool to find the minimum date. You can then append this date back to your full data set with an Append tool and use it to Filter for those dates that have DateFirst >= Min_Date. This should give you the dates/records you are looking for...

 

See attached example!

 

Cheers,

NJ

AmruthaPydeti
8 - Asteroid

Thank you very much for the response. I tried union and left join options but it didn't seems to work. I was missing Append Fields tool. And that worked now. thanks for the help Nicole.

AmruthaPydeti
8 - Asteroid