Admin at 00:19, 14 November 2015

2015-11-14T00:19:43Z

@@ Line 1: / Line 1: @@
-[[SSIS:Reference|&larr; SSIS Reference]]
+{{SSISMatchUpNav
+|MatchcodesCollapse=
-{| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; padding:8px 8px;"
+}}

Admin at 22:09, 21 November 2014

2014-11-21T22:09:49Z

← Older revision		Revision as of 22:09, 21 November 2014
Line 28:		Line 28:
	\|-		\|-
	\|[[SSIS:MatchUp:Options\|Options]]		\|[[SSIS:MatchUp:Options\|Options]]
			\|-
			\|[[SSIS:MatchUp:Golden Record\|Golden Record]]
	\|-		\|-
	\|[[SSIS:MatchUp:Survivorship Pass-Through\|Survivorship/Pass-Through]]		\|[[SSIS:MatchUp:Survivorship Pass-Through\|Survivorship/Pass-Through]]

Admin: Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."

2014-11-19T01:16:47Z

Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."

New page

[[SSIS:Reference|← SSIS Reference]]

{| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; padding:8px 8px;"
! style="text-align:left; color:black; border-bottom:1px solid #aaaaaa;"|'''MatchUp Navigation'''
|-
| style="padding-right:220px;"|
|-
|[[SSIS:MatchUp|Overview]]
|-
|[[SSIS:MatchUp Tutorial|Tutorial]]
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Advanced Configuration|Advanced Configuration]]
|-
|[[SSIS:MatchUp:Advanced Configuration#On-Premise|On-Premise]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|MatchUp Tabs
|-
|[[SSIS:MatchUp:Matchcode|Matchcode]]
|-
|[[SSIS:MatchUp:Field Mapping|Field Mapping]]
|-
|[[SSIS:MatchUp:Options|Options]]
|-
|[[SSIS:MatchUp:Survivorship Pass-Through|Survivorship/Pass-Through]]
|-
|[[SSIS:MatchUp:Lookup Pass-Through|Lookup Pass-Through Columns]]
|-
|[[SSIS:MatchUp:Output Filter|Output Filter]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Matchcode Editor|Matchcode Editor]]
|-
|[[SSIS:MatchUp:Matchcode List|Matchcode List]]
|-
|[[SSIS:MatchUp:Component List|Component List]]
|-
|[[SSIS:MatchUp:Component Properties|Component Properties]]
|-
|[[SSIS:MatchUp:Algorithms|Algorithms]]
|}
|-
|
{| class="mw-collapsible" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Matchcodes:Overview|Matchcodes Overview]]
|-
|[[SSIS:MatchUp:Matchcodes:Component Combinations|Component Combinations]]
|-
|[[SSIS:MatchUp:Matchcodes:Blank Field Mapping|Blank Field Mapping]]
|-
|[[SSIS:MatchUp:Matchcodes:Mapping|Mapping]]
|-
|[[SSIS:MatchUp:Matchcodes:Optimization|Optimization]]
|-
|[[SSIS:MatchUp:Matchcodes:Swap Matching|Swap Matching]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Result Codes|Result Codes]]
|-
|[[Returned Result Codes:Enterprise Solutions#Data Quality Components for SSIS|Returned Result Codes]]
|-
|[[Result Codes]]
|}
|}

{{CustomTOC}}

==Blank Field Matching==
This needs a special discussion, as its importance is often overlooked. If this property is on, then the absence of data in both records would indicate a match. If this property is off, then two records with missing data, but matching in every other way, will not match. It would be reasonable to wonder if you would not want this behavior. However, it is undesirable in certain situations.
Take the following matchcode (paying attention to the Blank column):

{|class="alternate01" cellspacing="0"
!Component
!Size
!Blank
!1
!style="border-right:0px;"|2
|-
|ZIP/PC
|5
|Yes
|X
|X
|-
|Last Name
|5
|Yes
|X
|X
|-
|Street #
|4
|Yes
|X
|
|-
|Street Name
|4
|Yes
|X
|
|-
|PO Box
|10
|Yes
|
|X
|}

As described above, this produces the following combinations:
;*Condition #1
:ZIP/PC, Last Name, Street Number, Street Name

;*Condition #2
:ZIP/PC, Last Name, PO Box

For this example, take the following records:

{|class="alternate01" cellspacing="0"
!align="right"|Name:
|Joe Smith
|Suzi Smith
|-
!align="right"|Address:
|326 Main Street
|405 Main St
|-
!align="right"|City/State/PC:
|Pembroke MA 02066
|Pembroke, MA 02066
|}

The following matchcode keys would be generated:

{|class="alternate01" cellspacing="0"
!Cond#
!ZIP/PC
!Last Name
!Street #
!Street Name
!style="border-right:0px;"|PO Box
|-
|1
|02066
|SMITH
|326
|MAIN
|
|-
|2
|02066
|SMITH
|405
|MAIN
|
|}

According to these matchcode keys, it is clear that these two records do not satisfy condition #1. But because blank field matching is selected, they do satisfy condition #2. The Zip/PC, Last Name, and PO Box are exactly the same. Therefore, the two records do match.

Obviously, this is not the correct result. Making one change to the matchcode:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Blank
!1
!style="border-right:0px;"|2
|-
|ZIP/PC
|5
|Yes
|X
|X
|-
|Last Name
|5
|Yes
|X
|X
|-
|Street #
|4
|Yes
|X
|
|-
|Street Name
|4
|Yes
|X
|
|-
|PO Box
|10
|No
|
|X
|}

The same comparison is done for combination #2, but the match is disallowed this time because the matchcode now indicates that missing (blank) information is not allowed to figure in the matching condition.

Looking at another example (using the same matchcode):

{|class="alternate01" cellspacing="0"
!align="right"|Name:
|Joe Smith
|Suzi Smith
|-
!align="right"|Address:
|PO Box 123
|PO Box 456
|-
!align="right"|City/State/PC:
|Pembroke MA 02066
|Pembroke, MA 02066
|}

This pairing produces the following matchcode keys:

{|class="alternate01" cellspacing="0"
!Cond#
!ZIP/PC
!Last Name
!Street #
!Street Name
!style="border-right:0px;"|PO Box
|-
|1
|02066
|SMITH
|
|
|123
|-
|2
|02066
|SMITH
|
|
|456
|}

This record has the same problem as before, but this time combination #1 is the cause.

An even better matchcode would be:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Blank
!1
!style="border-right:0px;"|2
|-
|ZIP/PC
|5
|Yes
|X
|X
|-
|Last Name
|5
|Yes
|X
|X
|-
|Street #
|4
|No
|X
|
|-
|Street Name
|4
|No
|X
|
|-
|PO Box
|10
|No
|
|X
|}

This is one matchcode that works well. There is one more possible tweak, however:
Turn on Both Blank Fields for the Street # component. Occasionally, MatchUP Object may encounter records such as:

{|class="alternate01" cellspacing="0"
!align="right"|Name:
|Joe Notarangello
|Suzi Notarangello
|-
!align="right"|Address:
|Oceanfront Estates
|Oceanfront Est.
|-
!align="right"|City/State/PC:
|Pembroke, MA 02066
|Pembroke, MA 02066
|}

This reflects a trend in up-scale neighborhoods, where neither street address has a Street # component, though it is very likely these records should match.

So this new improved matchcode will account for these situations:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Blank
!1
!style="border-right:0px;"|2
|-
|ZIP/PC
|5
|Yes
|X
|X
|-
|Last Name
|5
|Yes
|X
|X
|-
|Street #
|4
|Yes
|X
|
|-
|Street Name
|4
|No
|X
|
|-
|PO Box
|10
|No
|
|X
|}

[[Category:SSIS]]
[[Category:SSIS MatchUp]]

SSIS:MatchUp:Matchcodes:Blank Field Mapping - Revision history

Admin at 00:19, 14 November 2015

Admin at 22:09, 21 November 2014

Admin: Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."