How can I duplicate =VLOOKUP(L2,'[Paycheck Dates.xlsx]Check Date'!$A:$C,3,TRUE)

Hello,

I am trying to find a way to duplicate "=VLOOKUP(L2,'[Paycheck Dates.xlsx]Check Date'!$A:$C,3,TRUE)" within Alteryx. I was able to find examples of similar lookups where people used a common field to find specific information to append to a column. But no examples where someone needed to return a date based on pay periods (date range).

The Main File.xlsx is a redacted version of the table that needs to be updated. I am trying to retrieve the Check Date based on the Pay Date from the Paycheck Dates.xlsx file. Example using if/then which is not a practical solution as I need to look up the check date from multiple years (if Pay Date >= PPB and Pay Date <= PPE then Check Date...)

Thank you in advance

Main File.xlsx

Paycheck Dates.xlsx

Dynamic Processing

Accepted answers

LindonB

I think I see what you're trying to do. A join is essentially a fancy VLOOKUP with some nuances. (VLOOKUP returns the first occurrence only for example.)

Given that you can't join on a date falling in a range, you have two options: generate rows so that you have one record for all dates in the file containing the field you wish to join in OR join/merge not on the dates and then filter so that you only keep rows where the date falls between the min and max date.

See the attached file for your use case.

Hope that helps.

Solution.yxmd

All comments

aatalai

@GDavidson the join tool is your friend here (let me know if you would need more guidance)

GDavidson

If you don't mind I could use some more guidance. I am a fairly new Alteryx user.

Looking at the join tool I am not understanding how I would use it in this case. Can you add a formula to a join that will lookup the Pay Date from the Main excel file and return the check date from the row where Pay Date is >= PPB and Pay Date is <= PPE in the Paycheck Date excel file and append that date to the Pay Check field in the Main excel file?

EKasminsky

https://help.alteryx.com/current/en/designer/tools/join/find-replace-tool.html#find-replace-tool

You might find this tool easier to use than the Join one.

It sounds like you'll want to use a separate formula tool for the check date from the row where Pay Date is >= PPB and Pay Date is <= PPE.

LindonB

See the attached file for your use case.

Hope that helps.

Solution.yxmd

dreldrel

This article guides you on how the join tool works. Give it a try, but make sure you join the keys that are exactly the same.
https://www.thedataschool.co.uk/viktoriia-slaikovskaia/how-does-join-tool-works-in-alteryx/

jrlindem

@GDavidson An interesting challenge you've got for yourself. In reviewing responses, @LindonB's got the right idea (nicely done!).

Both of his options will solve your need; however, I would recommend using LindonB's Option 1 as it will keep the data more contained. Meaning, Option 2's forced cartesian (using the append tool) will create a ridiculously large dataset depending on how many pay-periods and main file records you are processing (Main File X Paycheck Dates). Option 1 is more optimal to avoid overly taxing memory.

Cheers and good luck in solving your overall business need! -Jay

GDavidson

Thank you all for all you help! What a great group!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703