Conditional join on Dates

[KanchanN]

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.

[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 · Accepted solution]

@KanchanN 
Hope this is what you need.

[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

[Qiu · Accepted solution]

@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.😁

[KanchanN]

Apologies@Qiu for typo.

thanks a lot for your suggestion , it worked thanks 😄

[Qiu]

@KanchanN 
Glad it works.

Thank you also for the feedback.