3 part join with a fuzzy on the date

[SteAllan]

Hello. I'm brand new to Alteryx, day 3 I think. Wondering if anyone can help me with a little problem

I have a 3 part join on two sets of data. Join is on the account number, a date, and a value.

Works fine, however the matching record might acutally have the date the day before (never after) or 2 days before, or 3, up to 10.

Alas I see the fuzzy match tool but no fuzzy join.

i'm struggling with the best logic to defeat his problem. Has anyone solved such a trick before, and if so how did you do it?

Any help would be much appreciated.

Best regards

Ste

[benakesh · Accepted solution]

Hi @SteAllan ,

Join   on account number and  value   . Add filter  to  'j'  output  from join  . In filter  check if date difference(DateTimeDiff)  is within 10 days .  If you need  working example  share  sample  data  .

[SteAllan]

Thanks Benakesh - i see what you mean. I get out of the j output multiple results, I just need to work out a filter formula to bring back only the one with the closest date (as there might be two).......

..... but this is a problem I should be able to work out.

Thanks again.

One day i'll have your rank 🙂

[ThizViz]

You could use the formula from Benakesh to create a column with the datediff. Then use a summarize tool to retrieve only the minimum datediff.

[SteAllan]

Hi ThizViz, that is initially what I tried to do.

However I wanted all the fields in my return, and Summarize seemed to require me to drop at least one.

So what I did in the end was:

Maded column with DateDiff in a Formula

Filtered to get >= 0 from that column (as I only interested in records that fuzzy matched with a EARLIER date)

Sort to put the DateDiff in Asc order by Account Number (my primary key), so I got the lowest DateDiff for each Account number first

Unique to get just the first record for each Account Number, which will be the one with the lowerest DateDiff thanks to the Sort

I then used a Union, to combine this output (which all started from the j in my Join) with the Right from my initial Join, and hey presto, I had me a Fuzzy Join (edit: I think. I hope)

I'll try to share just the Fuzzy Join bit