Match and No Match(Gives same output)
-
I would like to match this source table: CREATE TABLE [dbo].['TMI Sites $'](
[Advertiser] [nvarchar](255) NULL,
[Designated Market Area (DMA)] [nvarchar](255) NULL,
[DV360 Site] [nvarchar](255) NULL,
[Impressions] [float] NULL,
[Clicks] [float] NULL,
[Click Rate] [float] NULL,
[Interaction Rate] [float] NULL,
[Total Interactions] [float] NULL,
[Average Interaction Time] [float] NULL,
[Expansions] [float] NULL,
[Click-in Rate ] [float] NULL
) ON [PRIMARY]
GO
Against this lookup table:
CREATE TABLE [dbo].['Inclusion List $'](
[afpbb#com] [nvarchar](255) NULL
) ON [PRIMARY]
On Columns: DV360 Site - afpbb#com
I am using the Lookup function and connect that to a match and no match table. I am finding that data in the no match table also matches to my source data. That should not be the case.
- yrstruly wrote:
I would like to match this source table: CREATE TABLE [dbo].['TMI Sites $']( [Advertiser] [nvarchar](255) NULL, [Designated Market Area (DMA)] [nvarchar](255) NULL, [DV360 Site] [nvarchar](255) NULL, [Impressions] [float] NULL, [Clicks] [float] NULL, [Click Rate] [float] NULL, [Interaction Rate] [float] NULL, [Total Interactions] [float] NULL, [Average Interaction Time] [float] NULL, [Expansions] [float] NULL, [Click-in Rate ] [float] NULL ) ON [PRIMARY] GO
Against this lookup table:
CREATE TABLE [dbo].['Inclusion List $']( [afpbb#com] [nvarchar](255) NULL ) ON [PRIMARY]
On Columns: DV360 Site - afpbb#com
I am using the Lookup function and connect that to a match and no match table. I am finding that data in the no match table also matches to my source data. That should not be the case.
Please, when you make a post, use the code window. As you're typing the new post, you should see an icon menu above it like the following. Click on the item in the Red box and a code window will open. Paste your code there and then follow your nose.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The problem is that the no match output includes rows that you think should be in the match output, is that correct?
Do you have mixed upper and lower case text in either the table or the look-up? Or do you have untrimmed trailing spaces? SQL Server is typically set up to be not case sensitive, but SSIS is case sensitive. SQL will typically trim strings in making a comparison, SSIS does not. I suggest explicitly trimming both the data-flow columns and the look-up columns and casting them to upper case in order to perform the lookup (If you have not already done so).
For the look-up you can use a query instead of the table, with UPPER(LTRIM(RTRIM([ColumnName]))) AS 'ColumnName'
For the data-flow you can do the same at the source, or you can create a derived column to use in the look-up.
Another cause of failed look-ups is hidden characters. Sometimes there are tabs or other high/low asci characters in the data. If data has been pasted from MS Word, sometimes there are weird quote marks. Your data is unicode so you may have characters that look the same but are not. The upper/lower case is usually the culprit though.
-
My data source is an excel file which i imported to sql server.
- yrstruly wrote:
My data source is an excel file which i imported to sql server.
So what? The advice provided by Ed B is sound. Perhaps you could respond to the questions posed in that post?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I tried removing spaces and converted to uppercase case characters, still no difference in figures of match and no match.
-
Can you show us a screenshot of the Lookup configuration?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - yrstruly wrote:
I tried removing spaces and converted to uppercase case characters, still no difference in figures of match and no match.
Can you join your No Match destination table to the Look-Up table in SQL and use the ASCII function on apparently identical strings to see if there are subtle differences. I would start with something like this to see if my incoming data has some extended ascii chars not present in the look-up. This will only check the left and right-most characters. If necessary a numbers table allows you to compare every position in every string without looping, but I would check the basics first.
SELECT a.[DV360 Site],
b.[afpbb#com],
IIF(a.[DV360 Site] = b.[afpbb#com],1,0) AS SQLMatch,
ASCII(RIGHT(a.[DV360 Site],1)) AS NoMatchCharRight,
ASCII(RIGHT(b.[afpbb#com],1)) AS LookUpCharRight,
IIF(ASCII(RIGHT(a.[DV360 Site],1)) =ASCII(RIGHT(b.[afpbb#com],1)),1,1) AS ASCIIRightMatch
ASCII(LEFT(a.[DV360 Site],1)) AS NoMatchCharLeft,
ASCII(LEFT(b.[afpbb#com],1)) AS LookUpCharLeft,
IIF(ASCII(LEFT(a.[DV360 Site],1)) =ASCII(LEFT(b.[afpbb#com],1)),1,1) AS ASCIILeftMatch
FROM dbo.NoMatchDestination AS a
LEFT OUTER JOIN dbo.InclusionList AS b ON a.[DV360 Site] = b.[afpbb#com] -
Why is the column in the lookup table NULLable?
Is there actually a NULL value in it?
It's quite possible if you populate it from an Excel range.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply