Conditional join on Dates

Hi,

I have a requirement where i need to join 2 different tables with a set of fields using inner join including a date field.

Date logic should work as below:

TableA TableB

Date Amount A Date AmountB

01-Oct 787875 01-Oct 98888

08-Oct 9898989 08-Oct 34344

15-Oct 989844 15-oct 99999

28-Oct 343544 22-Oct 54555

Rows highlighted in green are joined with inner and they are fine.

4th row highlighted in red will have condition join where if TableA doesn't have match for 28th Oct then it should look back in previous week and whichever date is available then pick it hence it should pick 22 Oct.

can someone please advise me how can I achieve this using alteryx join tools?

Thanks a lot in Advance.

Developer

Accepted answers

Qiu

@KanchanN
Hope this is what you need.

1116-sheenu.yxmd

Qiu

@KanchanN

You can try to add the data by Tex Input tool or you could, update the data here.

Lets take a look.

PS: you metiond me wrongly.😁

All comments

MarqueeCrew

There is a macro in the gallery that will generate all dates between periods. https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14

you can use this macro to fill in dates that will be used in a join.

cheers,

mark

Qiu

@KanchanN
Hope this is what you need.

1116-sheenu.yxmd

KanchanN

Thanks@Kiu

This seems to be the correct flow to get the results.

I do have other fields which will need inner join , can I simply add them along with Date field in same workflow?

or that needs to be handled separately?

thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703