Matching 2 Datasets with Addresses to assign Thirdparty ID

[maria_yacoob]

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 ID	Primary Address	True Match in CS mapping	CSID
107257	205 Belfield Rd	TRUE	8098104
65123	260054 Range Rd 293
104774	2815 Argentia Rd	TRUE	8855077

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

Property Address	PropertyType	RBA	CS PropertyID
3348 58th Ave SE	Industrial	10120	9654650
3520-3534 48th Ave SE	Industrial	10080	9660228
3535 44th Ave SE	Industrial	10031	9658237
3624 44th Ave SE	Industrial	10197	9658209

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 ID	Primary Address	True Match in CS mapping	CSID	Matched CSID	Match Score	Matched Address	Matched Property Type	Matched RBA
65123	260054 Range Rd 293			No Match
65962	4030 7 St SE			No Match
66060	4156 64 Ave SE	FALSE	8884431	9717504	90+%	4156 64th Ave SE	Industrial	10284
66173	4300 116 Ave SE			9547200	90+%	4300 116th Ave SE	Industrial	10000

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

Maria

[shancmiralles]

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!