Vlookup coiumn from a date range based on two input files

[bryanmac_92]

Hello,

I have two input files. Both files shown below are truncated to show an example as there are over 1000 rows each. 

I am having trouble trying to fill in the blanks cells in Effective_Session, not sure how to go about it - whether to use a find and replace tool or something else.

It could run when I tried the find and replace tool, as it gave me an empty dataset.

The goal is to fill in the blank Effective_Session column by referencing the Date. If the Date [File 1] is within the range of the Start Date [File 2] and End Date [File 2], then Session [File 2] will be populated in Effective_Session column [File 1].

Usually the Date is varied and not just one day.

File 1

Date	Effective_Session
1/20/2023
1/20/2023
1/20/2023
1/20/2023
1/20/2023
1/20/2023
1/20/2023
1/20/2023

File 2

Session	Start Date	End Date
202311	8/29/2022	10/23/2022
202315	10/24/2022	1/8/2023
202321	1/9/2023	3/5/2023
202325	3/6/2023	4/30/2023
202331	5/1/2023	6/25/2023

Goal

Date	Effective_Session
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321
1/20/2023	202321

Thanks!

[ShankerV]

Hi @bryanmac_92 

Quick response to overcome the issue, when you have done Vlookup it didnt work as the date are not in Alteryx format.

We need to convert to date format and do the vlookup.

Let me know if you need to me to build a workflow to showcase how it works.

[Felipe_Ribeir0]

Hi @bryanmac_92 

One way of doing this:

[ShankerV]

Hi @bryanmac_92 

Please find the expected output.

[ShankerV]

Hi @bryanmac_92 

Another way by eliminating 2 other tools.

[bryanmac_92]

In looking at the Date column in seems that there are lots of cells that have this format: 2023-01-19.

What would be the workflow with this applied?

[ShankerV]

Hi @bryanmac_92 

The date is in Alteryx format YYYY-MM-DD (2023-01-19) to perform the calculations.

Edit: All the date's in the input are converted to Alteryx date format and then it is used to generate the output.

[binuacs]

@bryanmac_92 One way of doing this,if the Dates are in a proper format.