Loop through transactions and identify which groups transactions belong too based on dates

[dbmurray]

Hi all. I'm suffering from sleep deprivation and not thinking straight and trying to write what seems like an easy workflow. 

The scenario is:

I have a list of transactions I want to sort into groups based on their transaction dates. 

The 'groups' have a start date and end date, like so:

The transactions look like this:

I want to be able to place each transaction in all the groups they belong too. 

For example the Gray Duiker, I highlighted above, would belong in groups 2, 3, and 4 as its transaction date falls within the start and end dates of the groups.

This feels like an easy thing to do, but I've hit a bit of a mental block. Gut feel is that an iterative macro of some kind might be able to do it, but I'd prefer not to use one if possible.

Anyone keen to help an Alteryx brother out?

I've attached the source data if it helps!

Thanks in advance!

Darragh

[DataNath · Accepted solution]

Hey @dbmurray, how does something like this look? For each group, I've generated the full range between the start/end date and then conducted a join of dates based upon this. After the join, I've then rolled all records for each animal into one and concatenated the groups to bring back a single row with a list for each:

[Qiu]

@dbmurray 

We can use an Append tool but I suspect the year in your End Date should be 2020 in order to arrive the result in your sample?

[Qiu]

@DataNath 
Your solution might better considering it will be less calculation expensive. 👍

[dbmurray]

Thanks @datanat and @Qiu. Both solutions worked well but as Qiu says, Datanath's is slightly more elegant (in that it uses less tools). But thank you both for your contribution!

[Qiu]

@dbmurray 
Thank you for the feedback. I did also learned the solution from @DataNath