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 List | AD | SCCM |
1FASC53 | PRE-1FASC53 | PRE-1FASC53 |
6YSJUTA | POST-6YSJUTA | POST-6YSJUTA |
98LNAH7 | PRE-98LNAH7 | PRE-98LNAH7 |
4AJFB30 | SUFF-4AJFB30 | SUFF-4AJFB30 |
Solved! Go to Solution.
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.
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.
6YSJUTA | POST-6YSJUTA | PRE-1FASC53 |
The second and the third element have nothing in common. How should the Fuzzy Tool know that these belong together?
4AJFB30 | SUFF-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
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.
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.
Output:
Sample workflow attached. Let me know what you think.
Best
Alex
I think this is going to work for me. Thank you for your assistance!
Would you mind accepting it as a solution if this solves it? This will make it easier for others to find it.