DSXchange

Posted: **Wed Nov 01, 2006 11:17 pm**

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

Posted: **Wed Nov 01, 2006 11:30 pm**

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

Code: Select all

select col4, col5
from table2
where col 3 = 'source'

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?

Posted: **Thu Nov 02, 2006 9:02 am**

ShaneMuir 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 like
Code: Select all
select col4, col5
from table2
where col 3 = 'source'
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?

what you said is correct, I can achieve with this method, my problem is in table 2 (or hashfile) I have similar 30 combinations
(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)

Posted: **Thu Nov 02, 2006 10:10 am**

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 ""

Posted: **Thu Nov 02, 2006 11:59 am**

talk2shaanc 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 ""

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 know

Posted: **Thu Nov 02, 2006 12:20 pm**

Hi,

What are the keys for table 1 and table 2?

Thanks,
Whale.

Posted: **Thu Nov 02, 2006 12:47 pm**

I_Server_Whale wrote:Hi,

What are the keys for table 1 and table 2?

Thanks,
Whale.

In Table 1, col A is the key and in Table 2, col 1 is the key.
(NOTE:Table 2 is manually entered and maintained table)

Posted: **Thu Nov 02, 2006 1:00 pm**

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,

Code: Select all


colA to col1
'1' to col2
'source' to col3
colB to col4

I'm thinking this would work. Let me know if its not working.

Thanks,
Whale.

Posted: **Thu Nov 02, 2006 1:18 pm**

I_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,
Code: Select all
colA to col1
'1' to col2
'source' to col3
colB to col4
I'm thinking this would work. Let me know if its not working.

Thanks,
Whale.

Thanks for the reply
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

is it possible to achieve using one hash file rather than using 2 hash files
( 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

Posted: **Thu Nov 02, 2006 1:37 pm**

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.

Posted: **Thu Nov 02, 2006 2:23 pm**

ray.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 * ...

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 condition

Posted: **Thu Nov 02, 2006 7:07 pm**

[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

Code: Select all

---------------------------- 
colA|colB|colC|colD
---------------------------- 
1   |A   |1946|jlk

Posted: **Thu Nov 02, 2006 7:59 pm**

ShaneMuir wrote:[quote="prasad111
... my conclusion will be that it is not possible to implement the above question using only one HASH FILE,

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

Code: Select all

---------------------------- 
colA|colB|colC|colD
---------------------------- 
1   |A   |1946|jlk

[/quote]

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 
------------------------------

Posted: **Thu Nov 02, 2006 8:06 pm**

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?

Posted: **Thu Nov 02, 2006 8:42 pm**

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.

DSXchange

One more lookup problem

One more lookup problem