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

Fuzzy Tool Help - Comparing Data from 3 different sources

LTBP
5 - Atom

Good afternoon,

 

I'm trying to compare data from 3 different sources - our asset list, AD, and SCCM. I have all of the data imported already, and am trying to use Fuzzy Tool to compare data, since the data in our asset list is not exactly the same as the computer names listed in AD and SCCM. Our Asset list has just serial numbers, where as AD and SCCM have a prefix before the serial number. My ultimate goal is to have data sources sorted and in the same rows so we can ensure that our assets are where we think they are in our systems, and if they're not, where are they, etc. I'm not sure if that makes a whole lot of sense?

 

I can't figure out the settings in Fuzzy Match to get any kind of data output from it. My report so far looks like this


Data Sources -> Union -> Record ID -> Fuzzy Match

 

I've tried several combinations of settings in Fuzzy Match, and cannot seem to get the data to output.


Any suggestions?

 

Data Example (Fake data):

 

Asset ListADSCCM
1FASC53PRE-1FASC53PRE-1FASC53
6YSJUTAPOST-6YSJUTAPOST-6YSJUTA
98LNAH7PRE-98LNAH7PRE-98LNAH7
4AJFB30SUFF-4AJFB30SUFF-4AJFB30
7 REPLIES 7
grossal
15 - Aurora
15 - Aurora

Hi @LTBP,

 

could you also post the desired output to your sample data? Thanks!

 

Best

Alex

LTBP
5 - Atom

The desired output would look like the example above, I want data from all 3 columns to line up, and if the data is not in one of the columns for there to be a blank or a null in that row so we know it's missing data.

grossal
15 - Aurora
15 - Aurora

I am a bit confused. You could use the Fuzzy Tool if the columns have something in common or at least a part of it, but your second row is odd.

 

6YSJUTAPOST-6YSJUTAPRE-1FASC53

 

The second and the third element have nothing in common. How should the Fuzzy Tool know that these belong together?

 

4AJFB30SUFF-4AJFB30

PRE-1FASC53

 

Same with the fourth row. AD and SCCM have nothing in common. What's the rule behind this match?

 

 

We can easily line up Asset List and AD, you won't need the fuzzy tool for this, but what about the SCCM?

 

 

Best

Alex

LTBP
5 - Atom

Oops, that's my fault. In my haste to make the post I messed up the third Column. AD and SCCM information should be the same. It's comparing both to the data in the Asset List I'm having difficulty with. How would you suggest doing this without the Fuzzy Match tool? I tried removing prefixes from AD and SCCM data with no luck there as well.

grossal
15 - Aurora
15 - Aurora

You could use the Text To Columns tool and split it at the "-" and than use the second part to Join the data. You could also do some fancy Regex-Parsing if it's getting more complex, but I try to avoid it for simple cases.

 

grossal_0-1588269145364.png

Output:

grossal_1-1588269164387.png

 

 

Sample workflow attached. Let me know what you think.

 

Best

Alex

LTBP
5 - Atom

I think this is going to work for me. Thank you for your assistance!

grossal
15 - Aurora
15 - Aurora

Would you mind accepting it as a solution if this solves it? This will make it easier for others to find it.

Labels