Matching to the largest number smaller than X

[danago]

Hi. I have a column of consecutive dates:

Data set 1

Date

Jan 1

Jan 2

Jan 3

...

Dec 31

I have another data set where each row is an "activity", and the columns are "activity description" and "date"

Data set 2

Desc.	Date
Activity 1	Jan 1
Activity 2	Jan 3
Activity 3	Apr 23
Activity 4	Oct 14

What I want to do is create a new dataset, which is basically the same as "Data set 1" (i.e. the first column will be every date from Jan 1 to Dec 31), but the second column will be whatever the last activity to occur was (i.e. for a given row/date, what is the largest date in "Data set 2" that is less than the date in the new row). So, as an example, it would look something like this:

Data set 3

Date	Last activity
Jan 1	Activity 1
Jan 2	Activity 1
Jan 3	Activity 2
Jan 4	Activity 2
Jan 5	Activity 2
...	Activity 2
Apr 21	Activity 2
Apr 22	Activity 2
Apr 23	Activity 3
Apr 24	Activity 3

Is there a simple way to do this type of matching in Alteryx? 

Thanks in advance,
Dan

[Bob_Blackey · Accepted solution]

Hi Dan,

I've attached a workflow which does it.

You join the two sources together (by date) and then Union two of the outputs to get the full year of dates again.

Then with the Multi-Row Formula Tool you look at description field and if it is blank use the value from the previous row.

Cheers,

Bob

[pcatterson]

I approached it similarly, but instead of using a join, I generated the dates.  See attached.

[danago]

Thanks for these suggestion Bob and pcatterson - much appreciated!