Multiple values returned from the lookup
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
Multiple values returned from the lookup
Hi guys,
I need to do a look up against a database table and if multiple records are returned for the same keys, i need to upload another table with the keys that caused this in addition to the multiple records that are returned from them.
Can anyone help me out with this?
Thanks!
I need to do a look up against a database table and if multiple records are returned for the same keys, i need to upload another table with the keys that caused this in addition to the multiple records that are returned from them.
Can anyone help me out with this?
Thanks!
Last edited by datskosaraju on Tue Nov 10, 2009 7:35 am, edited 1 time in total.
"It's easier to go down a hill than up it but the view is much better at the top"
-Bennet,Arnold
-Bennet,Arnold
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
If duplicates are found for a key then , i need to just write the keys to a table.
For ex
For ex
Code: Select all
table1
key1 key2
A B
A C
A D
Table2
key1 key2 value
A B 1
A B 2
A c 3
o/p Table3
errorcol1
A+B returns duplicates //(varchar field concatenation of both the keys that has duplicates)
"It's easier to go down a hill than up it but the view is much better at the top"
-Bennet,Arnold
-Bennet,Arnold
Option 1: Use a join stage(inner join). Partition and sort the records based on the key fields in the input link of transformer Use stage variables and check if the current records keys match with the previous records keys- if yes write the error columns with the desired value (key1 : '+' : Key2 : 'returns duplicates') else leave it blank
Option 2: Use a sort stage with key change column created for the keys. then use a transformer. In it use a stage variable to check if the keychangcolumn=1 if yes then write the erro column with the desired value (key1 : '+' : Key2 : 'returns duplicates') else leave it blank
Option 2: Use a sort stage with key change column created for the keys. then use a transformer. In it use a stage variable to check if the keychangcolumn=1 if yes then write the erro column with the desired value (key1 : '+' : Key2 : 'returns duplicates') else leave it blank
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
-
- Premium Member
- Posts: 48
- Joined: Tue Nov 25, 2008 11:10 pm
- Location: Des Moines,IA
Do as per Option 2 above on the lookup stream. Doing it after the lookup sounds like a waste of processing time... and more so should your input stream be significantly larger than the likelihood of this happening.datskosaraju wrote:thanks for the reply,
I need to generate error not just because of the duplicate keys but if different values returned for the same set of keys!. ( in the ex keys A+ B returns values 1 & 2)