Validating records using Key Expression in transformer

[babbu9]

Hi
I am having a problem implementing a logic. I have a fixed width flat file which has Customer, Account,Group and Product IDs (all characters) as columns.
Firstly I would like to reject all records which have any of these values as Null.
Secondly I have to see that a concatenation of these 4 values already exists in a database and continue processing the record only if its present.
Ex: Customer1+Account1+Group1+ProductID1 is present in the database then only pass the record for further processing else reject it.

I have tried defining 4 values as keys in hash file (loading data from database) to do a equijoin in transformer, but that does n't seem to be working.

I have tried many different ways to execute the logic, but both cases seem to be failing in the job.

I would really appreciate if anyone could explain how to do this for me.

DESIGN
Database
|
hashfile
|
V
Tx------------->database
^
|
|
flatfile


Thanks
Bob[/img]

[ray.wurlod]

Your design as sketched is OK, but we can't see the stage and link properties.

Populate your hashed file with the four key columns. Make sure that they are all marked as key columns.

In your Transformer stage you will be required to enter four reference key expressions - presumably one from each relevant stream input column.

On the output link of the Transformer stage add a constraint expression to test whether the lookup succeeded or not. Either use the NOTFOUND link variable (with a Not() function), or test any of the returned key columns with an IsNull() function (again within a Not() function).

[Sainath.Srinivasan]

Some other checks can be
1.) Trim all fields that populate the hash-file (from db) as well as those which lookup into it (from ff)
2.) Store ALL resulting values from the lookup from both source and lookup links into a seq file to analyse better.

[andru]

Also make sure you do not have any numeric lookups on hash files. Convert all numeric fields to char fields before you do a lookup. For some reason, numeric lookups do not return proper value in hash file lookup.

[chulett]

Never had that problem. Don't really want to derail this thread, but you shouldn't have to worry about 'numeric lookups'.