community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Identify the next available date for a year and populate

Highlighted
Meteor

In the below table, for the 'Year' & 'Date' in row 1 it should first look for the matching year in 'Year2' and look for the next date in 'Date2' and that date to be populated in 'Output field'.

Row 3 Scenario: there is no next available date for 2018 in 'Date2' hence 'Output field' is  left blank.

 

S.noYearDateYear2Date2Output field
120171/1/201720171/1/20172/1/2017
220174/1/201720172/1/20175/1/2017
3201811/11/201820174/1/2017(should be blank)
4  20175/1/2017 
5  201811/11/2018 
6  20195/5/2019 
Bolide

You can just use a formula using the datetimeadd function:

Date1.PNG

 

Also attached

Andy

Bolide

You can wrap it into one formula too if you want, so output=

 

if [Year]=[Year2]
then DateTimeAdd(DateTimeParse([Date],"%d/%m/%Y"),1,'days')

else NULL()

endif

 

Just note the underlined field name, you can set this to either add a day to [Date] as per your output example, or [Date2] as per the question - whichever is the one you want... 

 

Andy

Meteor

@andyuttley 

Thanks. My table contains around 350 rows and it is consuming quite some time per your suggestion. Is there any other way to achieve this?

Bolide
How long is it taking to run?

I’d guess a formula like this would take < 2 seconds to run on 350 rows. Is there anything else in your workflow?
Feel free to attach here and I can take a look
Labels