look up is failing

harithay · Post by **harithay** » Fri Jun 10, 2005 10:23 am

hi all,

i have problem in lookup.

i have to load two tables from one input table.

the tables posses referential integrity .

i loaded table 1 succesfully to sql server database, in which primary key for the table(surrogate key) is generated automatically.

I need to load Table2 from input where primary key in table1 is foreign key in table2.

there are 20,000 records in input table. and table 1.

For loading table2 i am using Table 1 as look up to get the sequence from Table1 .

there are 5 key columns (combination of all columns is uniques).

when i run the second job , it runs successfully

i am able to load fileds from input table, i am not getting sequence (surrogate key from look up).

i am not getting match records on reference link.
But all records are unique in input and hashfile ( group by 5 columns)

What might be the problem.

Any suggetsions.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 10, 2005 12:25 pm

Did you check whether the rows are inserted in SQL Server for table1?

How are you doing the lookup? Are you referencing from SQL Server or writing to an interim hash-file?

In general, I think it is because your insert in source is not committed till the end and hence you are not able to see the changes.

harithay · Post by **harithay** » Sat Jun 11, 2005 8:24 am

hi
Thanks for ur reply 'sainath'

Yes , i have loaded table 1. i am uisng Hashed file for look up.

Source is Tab delimited flat file.

In hashed file output, i am able to see view data.

but it is not matchhing with Source .After excecuting job it is showing '0' rows on reference link. What might be the problem.

Thanks

chulett · Post by **chulett** » Sat Jun 11, 2005 9:35 am

harithay wrote:What might be the problem.

Well, how about all the normal stuff...

* Verify that the the keys you've chosen for the hash are appropriate
* Verify that you are mapping the right input source fields to their matching hash keys
* Verify there really is matching data between source and hash
* Verify that the hash file metadata used in the lookup matches exactly to what the hash looked like when created, especially key fields and column order.

Most common mistake when you think it "should" be matching and it isn't is a trim problem, especially when working with sequential files. The fields look the same but trailing spaces make them not match. For example, trying to match "ABC" to "ABC ". Make sure your key fields in the hash and the fields used in the Key Expressions are trimmed properly.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Sat Jun 11, 2005 10:15 am

Try to isolate lookup to confirm its working.

harithay · Post by **harithay** » Sat Jun 11, 2005 12:17 pm

Hi,
thanks for ur reply

yes i have checked everything what you have mentioned
absolutely there is matching between source and hashed file.
, meta data is also similar(exactly same) for both input and hashed file.

the hashed file is working. i am able to see the data inthe output through View Data.

other than this what might be the problem.
is it necessary to do trimming for both source and hashed file keys. please suggest me if u have any idea

chulett · Post by **chulett** » Sat Jun 11, 2005 7:42 pm

harithay wrote:is it necessary to do trimming for both source and hashed file keys.

You tell us, we can't see your data. You need to do whatever is necessary to ensure that the data matches across all keys - or, as you've found, the lookup will fail.

If that means trimming any character strings used in key fields, either when loading the hash file or populating the key expression, then trim one or both. If some numbers have leading zeros and some don't because of a metadata mismatch, then format them so they do match. All it takes is one of your what - five keys? - to be handled wrong to cause every row to fail the lookup.

When you view the hash data, you can click inside any of the character fields and see if there are any trailing spaces in it. I'm pretty sure, off the top of my head, you can do the same thing for the source rows via the Debugger.

If everything was checked and verified and proper, your lookup would be working. Since it's not, something is not as you assume it to be. Double-check everything about the process. Have someone else look at it, too - it's amazing what another pair of eyes might catch that you don't. We can only point you in the right direction, the solution is all in your hands.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Sun Jun 12, 2005 2:50 pm

Try deleting the hash-file and create it fresh or use a different hash file (name).

harithay · Post by **harithay** » Sun Jun 12, 2005 3:44 pm

Hi
Thanks sainath, i will try that

harithay · Post by **harithay** » Mon Jun 13, 2005 3:17 pm

Hi every one.

i got it

Thanks

chulett · Post by **chulett** » Mon Jun 13, 2005 3:21 pm

Can you let us know what, out of all of this, actually solved your problem?

harithay · Post by **harithay** » Mon Jun 13, 2005 4:17 pm

Hi ,

Silly mistake (Data Mismatch)

in this case . I am using Table 1 as look up to load Table2 from same source.

when i loaded Table 1 from source , i have done some mathematical manipulation to one of columns (multiply with 1000).

when i am trying to load Table2 from same source using Table 1 as look up .

in this case i got mismatch of data for that column

Ex:

Column name is Total_ net_ assets

if we take one record in Table 1 , it exists as 135000.00
same record in source exists as 1350.00.

this must be a minute mistake.

Any way Thanks for ur help.