Want to Match Date and amount Wise

[Srawat]

Hi,

I have Data A and Data B and the common thing is amount. however can we do like date and amount both lookup in Join tool.

If Data A  date 1/05/2022 and lookup in Data B next +5 days date or -5 days date and amount  is same then match. otherwise don't match.

+5day means- if 01/05/2022 then see between 01/05/2022 to 05/05/2022 and match. -5 days means it will need to see reduce dates. 01/05/2022 then 30/04/2022 .......

Thanks,

[Srawat]

[DataNath]

@Srawat you can't add a condition into a join and so here's how I'd go about this. As you say the join is based upon the amount, I'd first do that:

Then, I'd filter out those records where the 2 dates aren't within 5 days of one another. To do this, I've used the DateTimeParse() function to get your dates of dd/mm/yyyy into a format Alteryx can work with (YYYY-MM-DD). Then I've just took the absolute difference between the two and checked that it's either 5 or less. I've added another record with a difference of 10 days just to demonstrate this being excluded:

False anchor. >5 day difference:

[grazitti_sapna]

@Srawat , you can also achieve this using append tool. Here is my solution to this. Please accept if it helps.

[Srawat]

If I have already the date format in (YYYY-MM-DD) then what condition or formula will apply instead of below custom condition.

[DataNath]

Hey @Srawat, if your dates are already in the correct format then you can just use the following (essentially strip out the DateTimeParse() parts):

Abs(DateTimeDiff([Date],[Right_Date],'day')) <= 5

[Srawat]

Yes, It's working, however when I add one row highlighted in yellow first screen  and after run the workflow in second screen, we have duplicate line item created highlighted in circled and match. Why it's treating like this. please advise.

[DataNath]

@Srawat the reason you're seeing this duplicate is because the amount isn't unique. There are 2 records with a value of 5000 in the right hand table and so when you join, Alteryx will find 2 matches. Normally when conducting joins, you try and find unique keys or values to join on, such as IDs. In your initial post, you mentioned that the join needs to be conducted on amount so that's what I built.

[Srawat]

Ok Got it. Thanks, Can we sort out the issue or need to use different tool for same.

[DataNath]

How would you want to proceed here? If it's a case of not wanting those records to join, you'll need to try and find something within your dataset that is unique to conduct the join on, rather than amount. If it's another check that can be added based on some logic, then we can perhaps look to add another condition into the filter afterwards. It really depends on how you want to handle this unintended join/match.