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

Alteryx designer Discussions

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

Searching a particular date in an date range to append fields to Original File

Asteroid

HI All,

i have the following setup:

Main file: 

IDDate
11/15/2018
23/13/2017

 

Calendar:

YearQtrMonthStart DateEnd Date
2017Q2M12/1/20173/1/2017
2017Q2M23/2/20174/1/207
2017Q2M34/2/20175/1/2017
2018Q1M11/1/20181/31/2018

 

 i want to use the date from original file and search in the calendar table to find the corresponding Year, Qtr and Month Values. 

for 1/15/2018 it should pick 2018 Q1 M1 . Similarly for 3/13/2017 it should pick 2017 Q2 M2 and then append to the original file for each record. 

 

How can i achieve this.

 

Thanks

Sam

Alteryx Certified Partner
Alteryx Certified Partner

Here is how you can go about creating a solution.

1) convert all date times to actual date times using the datetime tool.

2) on your calender output, use the 'generate rows' tool to create a row for each date between and including the start/end date.

3) Join  our new date field created using the generate rows tool against your main dataset.

Solution attached.


Ben

Pulsar
 
Labels