Issue with lookup with only one key column

[nvkuriseti]

Hello,

Right now I am facing one issue during the job design. My source is oracle and have the following fields: ISO_COUNTRY3, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 and ADDRESS5. And my lookup hashed-file have the fields ISO_COUNTRY3 (Key), COMPANY_NAME. The business rule requirement is, scan all provided company names from lookup with source fields NAME, ADDRESS1 to ADDRESS5. If a match found then the customer should be identified as <OUTPUT>.Company = 'O'. The problem here is, since I've only one key column i.e., ISO_COUNTRY3 and there was no other key columns available. In the lookup concept, it will first to match with <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 then will find the <LKP>.COMPANY_NAME. But lookup does have lots of company_name with <LKP>.ISO_COUNTRY3 same value. Such as:

<LKP>.ISO_COUNTRY3 --> <LKP>.COMPANY_NAME
GBR --> XYZ LIMITED
GBR --> PHILIPS RADIO
GBR --> COMP SCIENCE LIMITED
.
.
.
.

Now, <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 matches then will return the value. But I've to check the <LKP>.COMPANY_NAME against <INPUT>.NAME, <INPUT>.ADDRESS1 to <INPUT>.ADDRESS5. Where here the input value not sure in which field it will be loading. For example, XYZ Limited value from Input field coming as <INPUT>.NAME = 'XYZ'; <INPUT>.ADDRESS1 = 'LIMITED'. If this will be the case I can't take COMPANY_NAME also as Lookup key column.

Need your suggestions If there was any possibility with different design. Thanks your help in advance.

[soumya5891]

can you please provide your Input and expected output and a brief description of your logic

[nvkuriseti]

Input:
=======
ISO_COUNTRY3 --> NAME --> ADDRESS1 --> ADDRESS2 --> ADDRESS3 --> ADDRESS4 --> ADDRESS5
GBR --> XYZ LIMITED --> <NULL> --> <NULL> --> <NULL> --> <NULL> --> <NULL>
GBR --> PHILIPS --> LTD --> AAA --> BBB --> CCC --> DDD
GBR --> XXX --> YYY --> HP --> COMPUTERS --> ZZZ --> WWW
.
.
.

Lookup:
=======
ISO_COUNTRY3 (Key column) --> COMPANY_NAME (Not Key column)
GBR --> ROCK STORES
GBR --> XYZ LIMITED
GBR --> PHILIPS LIMITED
GBR --> HP COMPUTERS
GBR --> EMMI STORES
.
.
.

Now, I've to match Input record with look-up and If lookup match found with Input record, my output column <OUTPUT>.COMPANY_NAME = 'O'. But my Input record is coming differently as mentioned above. Also, I've only one key column in look-up. So I've concatenate the Input record and match with lookup. But here, not sure the Input record value order for example as above.

[chulett]

I'm a little lost here. Hashed files do not allow duplicates per key and thus operate on a "destructive overwrite" basis - a.k.a "last one in wins". So you can't have five records in the hashed file lookup for the same key value.

Can you expand on what I assume is your workaround? I'm unclear what "I've concatenate the Input record and match with lookup" means and I don't really feel like guessing. Understanding that may help get you to a proper solution.

[nvkuriseti]

Chulett:

Thanks for your respond. Basically as my requirement is match Input record with lookup COMPANY_NAME and <LKP>.COMPANY_NAME successfully with input record then derive <OUTPUT>.COMPANY = "O". Here my input record is having 7 fields ISO_COUNTRY3, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 and ADDRESS5. But from above messages, I've given my Input record looks like. If so, how can I go ahead to match the Input record with lookup.

[chulett]

You just repeated information you've posted before and didn't answer my question. If you need to match on COMPANY_NAME then you'll need to build a different hashed file with that field as the key.