lookup with duplicate values in key column
Moderators: chulett, rschirm, roy
lookup with duplicate values in key column
Hi
I have a source file In which there are four key columns which is going to do a lookup on lookup table.
every indvidual looup key has duplicate values in the table.
This four keys are in hiearchy
Source
Transaction_type
Transaction_sub_type
Asset_id
If asset Id is populated then there should be value in all the above keys (source, transaction_type and transaction_sub_type)
simlarly for transaction_sub_type has value then Source and transaction should have value.
I'm trying to find the solution to load the data.
But when I do lookup I think it is picking only the first record from the table.
Here I want all the records with that key even though it has duplicates.
If key has 500 duplicate values I want to read all the 500 records.
Some one please help me in resolving this Problem
Thanks in Advance
Eze_ke
I have a source file In which there are four key columns which is going to do a lookup on lookup table.
every indvidual looup key has duplicate values in the table.
This four keys are in hiearchy
Source
Transaction_type
Transaction_sub_type
Asset_id
If asset Id is populated then there should be value in all the above keys (source, transaction_type and transaction_sub_type)
simlarly for transaction_sub_type has value then Source and transaction should have value.
I'm trying to find the solution to load the data.
But when I do lookup I think it is picking only the first record from the table.
Here I want all the records with that key even though it has duplicates.
If key has 500 duplicate values I want to read all the 500 records.
Some one please help me in resolving this Problem
Thanks in Advance
Eze_ke
Re: lookup with duplicate values in key column
Hi Eze_ke,
Why don't you go for join instead of lookup ?
Kris~
Why don't you go for join instead of lookup ?
Kris~
~Kris
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: lookup with duplicate values in key column
Eze_Ke,
Tell me if I understand your problem correctly.
You have a source file with 4 key columns in which some of the key columns are not populated. Say the column names are A,B,C, and D. If D is not null then A,B,and C would be populated as well. If D is null and C is not then A and B would not be null as well.
This means that 1) your keys are truly dynamic and you will want to perform a join based upon the information present in the data. Or 2) your table that you are performing a lookup would also have null values when there are null values in the key for your source.
Is 1 or 2 true? I think the answer will yield some insight for us to provide to you.
Tell me if I understand your problem correctly.
You have a source file with 4 key columns in which some of the key columns are not populated. Say the column names are A,B,C, and D. If D is not null then A,B,and C would be populated as well. If D is null and C is not then A and B would not be null as well.
This means that 1) your keys are truly dynamic and you will want to perform a join based upon the information present in the data. Or 2) your table that you are performing a lookup would also have null values when there are null values in the key for your source.
Is 1 or 2 true? I think the answer will yield some insight for us to provide to you.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
Hi Keith,
My keys are dynamic and they have duplicate values.
Since there are duplicates in lookup table I'm not sure I'm picking all the duplicate records.
In my case I have read all the duplicate records in the table
In my job when I'm doing lookup It is picking up the first duplicate record.
So I want to know is there any way to read all the duplicate records from lookup.
Thanks
Eze_ke
My keys are dynamic and they have duplicate values.
Since there are duplicates in lookup table I'm not sure I'm picking all the duplicate records.
In my case I have read all the duplicate records in the table
In my job when I'm doing lookup It is picking up the first duplicate record.
So I want to know is there any way to read all the duplicate records from lookup.
Thanks
Eze_ke
-
- Participant
- Posts: 28
- Joined: Mon Jan 09, 2006 1:31 pm
-
- Participant
- Posts: 28
- Joined: Mon Jan 09, 2006 1:31 pm
Hi,
I assume the following would work. But I am not very much confident about it.
Assume that your table names are M (main table), A (Asset ID), B (Transaction_Sub_type), C (Transaction Type) and D (Source).
What you can do is join M & A first, then join B with result, then C and finally D.
Hope it will work.
I assume the following would work. But I am not very much confident about it.
Assume that your table names are M (main table), A (Asset ID), B (Transaction_Sub_type), C (Transaction Type) and D (Source).
What you can do is join M & A first, then join B with result, then C and finally D.
Hope it will work.
With regards,
Thiru
Thiru
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
How do you have one lookup with a dynamic key? Is the key truly dynamic or do you allow nulls in your key?? Does your lookup table have nulls in the keys as well? If nulls are in both then your key is not dynamic, but it does allow null values. Still trying to understand your problem. Looking at the postings for solutions you have attempted it appears that we may not understand fully your logic. A join would create a cartesian product if there were multiple keys there.eze_ke wrote:Hi Keith,
My keys are dynamic and they have duplicate values.
Since there are duplicates in lookup table I'm not sure I'm picking all the duplicate records.
In my case I have read all the duplicate records in the table
In my job when I'm doing lookup It is picking up the first duplicate record.
So I want to know is there any way to read all the duplicate records from lookup.
Thanks
Eze_ke
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
Re: lookup with duplicate values in key column
Hi All,
Couple of clients asking Same question in the interview in case of Hashfile stage.
Please let me know it will load all duplicates or only first record.
Thanks
ceenu
Couple of clients asking Same question in the interview in case of Hashfile stage.
Please let me know it will load all duplicates or only first record.
Thanks
ceenu
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: lookup with duplicate values in key column
Hash files are server job and your are posting in the parallel job forum. To answer your question a hash file will eliminate duplicates. It is one of the most common ways to eliminate duplicates in a server job.ceenu wrote: Couple of clients asking Same question in the interview in case of Hashfile stage.
Thanks
ceenu
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
Re: lookup with duplicate values in key column
Thanks,sorry about posting in parallel.
You mean it will pickup only first record
thanks
ceenu
You mean it will pickup only first record
thanks
ceenu
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: