Filter and merge data based on string (month)

[mystasz]

Hi Community,

I have two datasets which I created a sample of in the attached file.  

Input 1 - Contains people and their anniversary month. 

Input 2 - Contains the total hours worked per month for all months possible data is available for. The "End Period Date" column is a string value (e.g January_2021). 

What I'm trying to do:

- Based on the persons anniversary month, I'm trying to merge the data sets to only provide data from input 2 if it falls within their anniversary month based on YTD data.

- Since we're in 2023 and Charles's anniversary month is May, I would need data from May 2022 - April 2023. Everything else for Charles is irrelevant. In 2024, the same workflow will pull May 2023 - April 2024

- For Joan, her anniversary month is June so I would need June 2022 - May 2023 data. Everything else is irrelevant until we run it again next year. 

- For Henry, his anniversary month is January so I would need January 2022 - December 2023.

In the 'Input 2' tab, I've highlighted all the relevant dates. "Expected Output" tab contains what I am trying to accomplish. How would you do this?

[Prometheus · Accepted solution]

The first thing I did was parse Input 2 on the underscore ("_"), creating two fields which I renamed with a Select tool to Month and Year. After that, I created a simple table with the month and month number with 01 for January, 02 for February, etc. I did this so I could bring in the month number with a Join and create a fake date that equals the first of the month for all months in the data. After that, I brought in Input 1 with a join on Name and Anniversary Month. I filtered out the data coming from the J output of the join so only records where the year equals "2023" passed. Then I used an Append Fields tool to bring in the data coming from the R output of the Join tool and unioned it to the non-2023 data that came out of the J output. I used a Formula tool after that to determine "Diff," which is the difference in months between anniversary date (Date) and Source_Date, which are the unjoined records and non-2023 records. A filter tool after that allows only records where the Diff >=1 and Diff <=12, ensuring only the 12 months leading up to the anniversary month in 2023 are passed. I finished it off with a Sort tool and sorted on Name - Ascending and Diff - Descending so you can see the records by month.

**Note: I did find that for every occurrence of "June" there was no underscore and a space instead. I changed it because I thought maybe it was an error, but if that's not the case, then you can use Replace to switch out the space with an underscore.

 

 

[mystasz]

This is great and easy to follow. Thank you.