One more lookup problem
Moderators: chulett, rschirm, roy
One more lookup problem
I am having tough time with lookup,
table 1
----------------------------
colA | colB | col C | col D
----------------------------
1 | A | 1946 |jlk
2 | B | 946 |jlk
3 | A | 46 |jlk
4 | B | 6 |jlk
5 | A | 1 |jlk
6 | D | 146 |jlk
7 | E | 196 |jlk
----------------------------
table2
----------------------------
col1|col2|col3 |col4|col5
----------------------------
1 |1 |Source|A |1
2 |1 |Source|B |2
3 |1 |Source|C |3
4 |1 |Source|D |4
5 |1 |Source|E |5
6 |2 |select|A |2
7 |2 |select|B |3
8 |2 |select|C |4
9 |2 |select|D |5
10 |2 |select|E |6
----------------------------
table1 is the source table and table 2 is the lookup table
I am trying to achieve this condition
if ((table1.colb = table2.col4) AND (table2.col3 = source)) then table 2.col5 else ""
output
----------------------------
colA | colB | col C | col D
----------------------------
1 | 1 | 1946 |jlk
2 | 2 | 946 |jlk
3 | 1 | 46 |jlk
4 | 2 | 6 |jlk
5 | 1 | 1 |jlk
6 | 4 | 146 |jlk
7 | 1 | 196 |jlk
----------------------------
The problem here is I dont have the common key between table 1 and table 2 how can i achieve this.
NOTE: I have to use table2, it cannot be hardcoded in the transformer
table 1
----------------------------
colA | colB | col C | col D
----------------------------
1 | A | 1946 |jlk
2 | B | 946 |jlk
3 | A | 46 |jlk
4 | B | 6 |jlk
5 | A | 1 |jlk
6 | D | 146 |jlk
7 | E | 196 |jlk
----------------------------
table2
----------------------------
col1|col2|col3 |col4|col5
----------------------------
1 |1 |Source|A |1
2 |1 |Source|B |2
3 |1 |Source|C |3
4 |1 |Source|D |4
5 |1 |Source|E |5
6 |2 |select|A |2
7 |2 |select|B |3
8 |2 |select|C |4
9 |2 |select|D |5
10 |2 |select|E |6
----------------------------
table1 is the source table and table 2 is the lookup table
I am trying to achieve this condition
if ((table1.colb = table2.col4) AND (table2.col3 = source)) then table 2.col5 else ""
output
----------------------------
colA | colB | col C | col D
----------------------------
1 | 1 | 1946 |jlk
2 | 2 | 946 |jlk
3 | 1 | 46 |jlk
4 | 2 | 6 |jlk
5 | 1 | 1 |jlk
6 | 4 | 146 |jlk
7 | 1 | 196 |jlk
----------------------------
The problem here is I dont have the common key between table 1 and table 2 how can i achieve this.
NOTE: I have to use table2, it cannot be hardcoded in the transformer
Is the value of table2.col3 literally 'Source'?
If so select the values from that table into a hash file with two columns. Use a query like
On the hash file set the first column (col4) as the key. Link this column to your input table. If hashlookup.col4 = input.colb then hashlookup.col5 else ''
Make sense?
If so select the values from that table into a hash file with two columns. Use a query like
Code: Select all
select col4, col5
from table2
where col 3 = 'source'
Make sense?
what you said is correct, I can achieve with this method, my problem is in table 2 (or hashfile) I have similar 30 combinationsShaneMuir wrote:Is the value of table2.col3 literally 'Source'?
If so select the values from that table into a hash file with two columns. Use a query likeOn the hash file set the first column (col4) as the key. Link this column to your input table. If hashlookup.col4 = input.colb then hashlookup.col5 else ''Code: Select all
select col4, col5 from table2 where col 3 = 'source'
Make sense?
(For example
select col4, col5 from table2 where col 3 = 'source'
select col4, col5 from table2 where col 3 = 'select'
select col4, col5 from table2 where col 3 = 'xxxxx'
select col4, col5 from table2 where col 3 = 'YYYYY'
\\
\
\
\ long set)
instead of creating 30 hash files, I will create the hash file for the table2 and I have to use that for future use rather than creating a new hash file.. this is my requirement (sorry for not mentioning in the question before)
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
If I use key as col4 in table 2 then the duplicates will be removed, that will miss lot of records, .............thats what I understood from what you said, if I am wrong let me knowtalk2shaanc wrote:You can write table 2, to hash file with Key as col4. Do a look on T1MainStream.colb = T2LkupLink.col4.
and later in stage variable, have Not(T2LkupLink.NOTFOUND) AND (T2LkupLink.col3 = source)) then table 2.col5 else ""
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Hi,
If you create a hashed file for Table2 with a composite key consisting of ( col1, col2, col3 and col4) Then you would have all the records.
Now coming to the transformer of the job. Perform the lookup, by joining,
I'm thinking this would work. Let me know if its not working.
Thanks,
Whale.
If you create a hashed file for Table2 with a composite key consisting of ( col1, col2, col3 and col4) Then you would have all the records.
Now coming to the transformer of the job. Perform the lookup, by joining,
Code: Select all
colA to col1
'1' to col2
'source' to col3
colB to col4
Thanks,
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Thanks for the replyI_Server_Whale wrote:Hi,
If you create a hashed file for Table2 with a composite key consisting of ( col1, col2, col3 and col4) Then you would have all the records.
Now coming to the transformer of the job. Perform the lookup, by joining,
I'm thinking this would work. Let me know if its not working.Code: Select all
colA to col1 '1' to col2 'source' to col3 colB to col4
Thanks,
Whale.
What you said is correct, I am using in the same way, my concern is,
1.in some of the source tables (in this case table1) i dont have the key that is colA then what should I do.
2.If I want to use the same lookup in the same datastage job For example
Code: Select all
colA to col1
'1' to col2
'source' to col3
colB to col4
Code: Select all
'1' to col2
'select' to col3
colB to col4
( as i said the table 2 is manually entered table, in one of my DSJob I have to use around 8 lookups to achieve it) how can I achieve this
Please help me in this
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Fundamental Principle
It is not possible to combine rows from multiple sources unless there is some common factor, without generating a Cartesian product.
A Cartesian product contains M * N rows, where there are M rows in one source and N rows in the other source. You don't really want to go there.
It is not possible to combine rows from multiple sources unless there is some common factor, without generating a Cartesian product.
A Cartesian product contains M * N rows, where there are M rows in one source and N rows in the other source. You don't really want to go there.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks for the reply, As per your comment my conclusion will be that it is not possible to implement the above question using only one HASH FILE, ............................we have to use multiple hash file to achieve this conditionray.wurlod wrote:Fundamental Principle
It is not possible to combine rows from multiple sources unless there is some common factor, without generating a Cartesian product.
A Cartesian product contains M * ...
[quote="prasad111
... my conclusion will be that it is not possible to implement the above question using only one HASH FILE, [/quote]
It can be done with one hash file - but you would just have to refer to that one hash file 30 times.
However - i am just wondering if I understand what you are trying to achieve. For the input of Table1.colB you wish to return the values from table2.col5 whenever table1.colB=table2.col4. Or are there values in table2.col3 that you which to exclude by? Could you please give an example of the output you would expect for the input from table 1
... my conclusion will be that it is not possible to implement the above question using only one HASH FILE, [/quote]
It can be done with one hash file - but you would just have to refer to that one hash file 30 times.
![Smile :)](./images/smilies/icon_smile.gif)
However - i am just wondering if I understand what you are trying to achieve. For the input of Table1.colB you wish to return the values from table2.col5 whenever table1.colB=table2.col4. Or are there values in table2.col3 that you which to exclude by? Could you please give an example of the output you would expect for the input from table 1
Code: Select all
----------------------------
colA|colB|colC|colD
----------------------------
1 |A |1946|jlk
It can be done with one hash file - but you would just have to refer to that one hash file 30 times.ShaneMuir wrote:[quote="prasad111
... my conclusion will be that it is not possible to implement the above question using only one HASH FILE,
![Smile :)](./images/smilies/icon_smile.gif)
However - i am just wondering if I understand what you are trying to achieve. For the input of Table1.colB you wish to return the values from table2.col5 whenever table1.colB=table2.col4. Or are there values in table2.col3 that you which to exclude by? Could you please give an example of the output you would expect for the input from table 1
Code: Select all
----------------------------
colA|colB|colC|colD
----------------------------
1 |A |1946|jlk
Code: Select all
Table 1
----------------------------
colA|colB|colC|colD
----------------------------
1 |A |1946|jlk
--------------------------
Table 2(LookUp-hashFile)
----------------------------
col1|col2|col3 |col4|col5
----------------------------
1 |1 |Source|A |1
2 |1 |Source|B |2
3 |1 |Source|C |3
4 |1 |Source|D |4
5 |1 |Source|E |5
6 |2 |select|A |2
7 |2 |select|B |3
8 |2 |select|C |4
9 |2 |select|D |5
10 |2 |select|E |6
----------------------------
when (Table1.coB = Table2.Col4) and (Table2.col3 = source) then Table2.col5
the above code will be in column derivation (colB)[NOTE: The above example will not get the output]
Output
----------------------------
colA | colB | col C | col D
----------------------------
1 | 1 | 1946 |jlk
------------------------------
---------------------------------------------------------------------------------------second example-----------------
Table 1
----------------------------
colA|colB|colC|colD
----------------------------
5 |A |1946|jlk
--------------------------
Table 2(LookUp-hashFile)
----------------------------
col1|col2|col3 |col4|col5
----------------------------
1 |1 |Source|A |1
2 |1 |Source|B |2
3 |1 |Source|C |3
4 |1 |Source|D |4
5 |1 |Source|E |5
6 |2 |select|A |2
7 |2 |select|B |3
8 |2 |select|C |4
9 |2 |select|D |5
10 |2 |select|E |6
----------------------------
when (Table1.coB = Table2.Col4) and (Table2.col3 = select) then Table2.col5
the above code will be in column derivation (colB)[NOTE: The above example will not get the output]
Output
----------------------------
colA | colB | col C | col D
----------------------------
1 | 2| 1946 |jlk
------------------------------
But how are limiting the output?
Each time in your examples, you are only showing one output but you have said that its possible the constricting value could be 'source' or 'select' or many others. How are you determining which value the output is to be restricted by? Ie Are you only ever wanting one output or are you wanting more than one?
Each time in your examples, you are only showing one output but you have said that its possible the constricting value could be 'source' or 'select' or many others. How are you determining which value the output is to be restricted by? Ie Are you only ever wanting one output or are you wanting more than one?
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
1. Write to hash file with keys as table2.col4 and table2.col3
2. Use UtilityHashLookup routine to read the hash file
3. Check return value of the routine
If IsNull(UtilityHashLookuptable(Arg1,Arg2,Arg3)) Then "" else table2.col5
Note : you need to add hash file to VOC, before reading it through this routine.
2. Use UtilityHashLookup routine to read the hash file
3. Check return value of the routine
If IsNull(UtilityHashLookuptable(Arg1,Arg2,Arg3)) Then "" else table2.col5
Note : you need to add hash file to VOC, before reading it through this routine.
Shantanu Choudhary