Lookup Formula or Tool Needed

[JBO]

Hello - I need help figuring out a lookup problem. 

Table 1:  A list of Pay Dates

Table 2:  Daily timekeeper data with Week Start and Week End Date fields.

The goal:  To find the Pay Date in Table 1 that is greater than the Week Start Date in Table 2 and no more than 13 days from from the Week End Date in Table 2.

[Week Start Date] < [Pay Date] and

DateTimeDiff([Pay Date], [Week End Date], 'Days') <= 13

Expressed differently, I need a way to tell the conditional statement to search Table 1 for a date that matches the conditions above. I am not sure if this is done via a function that I am just not familiar with or with a tool. Most of the discussions on Lookup seem to point everyone to the Join or Find Replace tools, but I am not seeing a way in either of those to apply a condition before looking for the value.

Thanks for any guidance you can provide. 

[JBO]

Also, can someone please tell me why Post labels are so finicky. I tried tagging this with lookup and it rejected it. I tried multiple other labels and rejected all of them. So I just grabbed some labels from someone else's posts and they worked.

[alexnajm]

Can you provide sample data for us so we can tackle this better? Dummy data is fine too

[JBO]

Attached. Thank you. This is a much smaller data set.

[alexnajm · Accepted solution]

Made a few assumptions on logic (i.e. what if the logic found multiple pay dates) and what you wanted your final output to be, but here you go:

[JBO]

Thank you, Alex. I had considering doing it this way, but was hoping there would be a way to simply do a lookup of the Pay Dates list rather then append and generate many more rows and then purge the rows that don't meet the condition. I appreciate the time and help

If anyone knows of a lookup method in Alteryx that allows for conditions, that would be amazing. There are many flows in which I could use this functionality.

[alexnajm]

@JBO no problem! If you could accept it as a solution then, that'd be great.

In theory, you could package this into a batch macro and do each ID individually, but it'll follow similar logic with the Append since there's no common field to join on (Join tool) or match on (Find Replace tool). The only thing that it might help with is runtime if your actual datasets are MUCH larger.

[JBO]

I would love a tool to be created for this. It could be called "Lookup." One input to the tool would be the lookup list, the other is the main database. Inside the tool you could enter functions that can query the lookup table and return the results either as an overwrite of an existing field in the main DB or as a new field in the main DB.

It would clean up my flows considerably.

I appreciate the help. Thanks!

[alexnajm]

Suggest it as an idea then! Alteryx Designer Desktop Ideas - Alteryx Community