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.
Issue with lookup with only one key column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 38
- Joined: Sat Jan 17, 2004 12:29 am
- Location: Hamburg, Germany
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
Re: Issue with lookup with only one key column
can you please provide your Input and expected output and a brief description of your logic
Soumya
-
- Premium Member
- Posts: 38
- Joined: Sat Jan 17, 2004 12:29 am
- Location: Hamburg, Germany
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.
=======
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 38
- Joined: Sat Jan 17, 2004 12:29 am
- Location: Hamburg, Germany
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.
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.