lookup returns multiple rows

[cognos11]

I posted this sometime back, but before i could start working on the solution, i had to switch to another project.
Now i am back to fixing this problem and i have been having difficulty in comprehending the replies that i got

I would appreciate if anyone could reply with little more detail as to how to use the ports in expression and aggregator.

My source table called visit table has a visitid which will do a lookup to name table using the condition

visit.visitid = Name.visitid, but it returns multiple rows for the same visitid like below.

I have been suggested to use Name table as the source instead of as the lookup and use a joiner and then expression and aggregator.

I am not able to figure out as to how to do this in expression and aggregator.


Any as to how to do this in expression and aggreator.
a step by step solution , i know its asking too much, but it will be greatly appreciated.

visitid name value
10006795 website yahoochat
10006795 SIZE 728
10006795 PLACE north

10006799 website rocketmail
10006799 SIZE 729
10006799 PLACE south




Target Table
------------

visitid size place website

10006795 728 north yahoochat
10006799 729 south rocketmail

 

[blom0344]

Well ,I think that I replied to your earlier post in quite some detail...

What you want is the opposite to the normalizer. It is not yet available in Powermart, but probably will be in version 7 or so.

The work around is as follows:

step 1: expression

Create 3 outgoing ports for website, size , place, like:

IIF(name = 'website',value,null)

Now you will still have 3 rows for any visitid.
To compress to one row, use :

step 2: aggregator:

use group by on visitid. The 3 outgoing ports from the expression are ingoing ports for aggregator.

create outgoing port for each incoming port in aggregator with expression:

= max(ingoing port)

It is irrelevant if you use min or max here, cause there is only one value available for each visitid.

I cannot explain in any more detail, this is VERY basic stuff




T. Blom
Information analyst
tbl@shimano-eu.com

 

[mpcurry]

Hi,

I'm new to informatica, but I think I can solve your problem by using SQL skills :

1) Take visit as your source table.
2) Amend your source qualifier to overide the sql with following :
select visit_id,
max(decode(name,'website',value,null)),
max(decode(name,'size',value,null) ),
max(decode(name,'place',value,null) )
from visit
group by visit_id
3) Link the output of the query to your target table.

Please let me know if you have any issues with this solution.

Regards
Ajay.

 

[mpcurry]

Forget it..Its difficult to implment and moreover it is exaclty the same as suggested by T.Blom.

 

[blom0344]

It is certainly NOT the same !!

I do not think your solution will be possible since number of incoming and outgoing ports should be the same in source qualifier.......

T. Blom
Information analyst
tbl@shimano-eu.com

 

[mpcurry]

Yes you are right, we won't be able to implement this. By same I meant that my query is also doing the same thing :
1) It creates three fields (website,size and place) on the basis of value in the value field.
2) Perform the aggregation using MAX function.

Regards

 

[cognos11]

Guys,
Thanks for your replies.I used the logic suggested by Ajay ie using doing a Max(Decode) function,however, i created it as view instead of using it as sql override.

It works perfectly fine.