Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Community is experiencing an influx of spam. As we work toward a solution, please use the 'Notify Moderator' option on the ellipsis menu to flag inappropriate posts.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

dbmurray
8 - Asteroid

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:

 

dbmurray_0-1666094136865.png

 

The transactions look like this:

dbmurray_1-1666094173837.png

 

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

 

5 REPLIES 5
DataNath
17 - Castor
17 - Castor

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:

 

DataNath_0-1666095137257.png

Qiu
21 - Polaris
21 - Polaris

@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?

1018-dbmurray.PNG

Qiu
21 - Polaris
21 - Polaris

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

dbmurray
8 - Asteroid

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
21 - Polaris
21 - Polaris

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

Labels