Want to Match Date and amount Wise

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,

Data Investigation

Accepted answers

All comments

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:

Join on amount and check datediff.yxmd

grazitti_sapna

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

test.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703