Matching 2 Datasets with Addresses to assign Thirdparty ID

Question

Hi,

I'm very new to Alteryx and I'm trying match my internal data with an external ID. I have 2 Datasets both containing Addresses.

My Internal data (dataset 1 - Int Mapping) have some Addresses matched to a CSID (external ID from thirdparty data), while there are some rows with 'False' or 'Blank' under the 'True Match in CS Mapping column' with an incorrect ID or no ID.

Int Property IDPrimary AddressTrue Match in CS mappingCSID107257205 Belfield RdTRUE809810465123260054 Range Rd 293  1047742815 Argentia RdTRUE8855077

I also have table from the data provider with CSIDs (CS PropertyID):

Property AddressPropertyTypeRBACS PropertyID3348 58th Ave SEIndustrial1012096546503520-3534 48th Ave SEIndustrial1008096602283535 44th Ave SEIndustrial1003196582373624 44th Ave SEIndustrial101979658209

To increase the number of matches with a CS Data table, I would like to fuzzy match the internal data to CS Data table for the False and Blank 'True Match in CS Mapping' column, to get potential matches, its matching score and the corresponding CS Address etc..

Int Property IDPrimary AddressTrue Match in CS mappingCSIDMatched CSIDMatch ScoreMatched AddressMatched Property TypeMatched RBA65123260054 Range Rd 293  No Match    659624030 7 St SE  No Match    660604156 64 Ave SEFALSE8884431971750490+%4156 64th Ave SEIndustrial10284661734300 116 Ave SE  954720090+%4300 116th Ave SEIndustrial10000

Attached are the full tables with all the columns. Can someone please help with this? Thanks in advance!

Maria

CS Data.xlsx

Desired Output.xlsx

Int Mapping.xlsx

shancmiralles · Accepted Answer

hi!

1) INT Mapping connect a (data cleansing tool )  
 (Remove Unwanted Characters: Leading and Trailing Whitespaces) . connect a (select tool)  (uncheck all columns except for Primary Address).

2) CS DATA connect a (data cleansing tool )  
 (Remove Unwanted Characters: Leading and Trailing Whitespaces) . connect a (select tool)  (uncheck all columns except for Property Address).

3) merge your 1 & 2 with a (union tool ) 
 (Auto Config by Position) connect a (Fuzzy Match tool)  and connect this to the Left input anchor of a (join tool) 
 while connect the INT MAPPING file on the right input anchor.

Under the  configuration:

Left: Primary Address

Right: Primary Address

Uncheck: Left  - Primary Address

Combine the the Middle/Join output anchor and Right output anchor using a (union tool ) 
 (Auto Config by Name) and connect this to  the Left input anchor   of a new (your 2nd) (join tool) . Connect your CS DAta file to right input anchor of this new 
.

Under the  configuration:

Left: Primary Address2

Right: Property Address

Uncheck The columns you don't need and move the ones you need to organize.

your CS PropertID column is your "Matched CSID" on your desire output file .. to do this.. you can use a a formula tool  and don't add a column just click the arrow down and find the "CS PropertyID" and use th ff formula:

if IsNull([Primary Address]) then "No Match" else [CS PropertyID] endif

you will encounter a formula error , you need to go back to the last join tool and change the data type of the  "CS PropertyID"  from double to V_WString.

Hope this helps!