Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

3 part join with a fuzzy on the date

SteAllan
7 - Meteor

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

4 REPLIES 4
benakesh
12 - Quasar

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
7 - Meteor

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
11 - Bolide

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.

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
SteAllan
7 - Meteor

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

 

 

 

 

Labels