DSXchange

Posted: **Mon Jan 16, 2006 11:12 am**

Hi !

I am having a problem with the Lookups.

Source File has 1 record (recordid)
Lookup Table (RecordId, RecordName, RecordDetails)
Target (File)

I need to read the source file, lookup for values in the LookupTable where LookupTable.RecordId <= SourceFile.RecordID and populate the resultant rows in the Target file.

I have done a search on "Range Lookup", but couldnt find anything that would give a simple way of doing this.

Could you please suggest a way.

Regards
Nagesh

Posted: **Mon Jan 16, 2006 11:45 am**

I would reverse the tables and use the table with multiple records as your source table. Use the other record as a lookup with a hard coded Record ID and use the constrants to filter out records you do not need.

Posted: **Mon Jan 16, 2006 11:59 am**

Hi !

The problem is with defining the constraint

Code: Select all

                        Lookup (Singlerecord)
                                      | lnk2
                                      |
                                      v
Src(multiRecordTable)  ----------> Transformer ---------------> Target
                         lnk1                             lnk3

What do I mention in the "KeyExpression" part of the lookup.

Posted: **Mon Jan 16, 2006 12:23 pm**

You can hard code the key as a string. If it changes with each run, then take the record (single record item) and write it to a hashed tble with a hard coded key field>

Hashed table with following fields:
Key Field - "Record" (any string to identify the record.
Field 1 - RecordId (from the original table)

Then in your job, put the hashed file key in the KeyExpression "Record"

Make sure you preload the hash table to memory.

Stephen

DSXchange

Database Lookup

Database Lookup