update strategy puzzle

[bittmapp]

I am trying to insert and / or update a target from a flat file that is set up like this.

PK indicator value
1 revenue 100
1 cost 200
1 percentage 50

What I want the target to show is the following

PK revenue cost percentage
1 100 200 50

What is the easiest way to get this done.

I have tried a mapping with a lookup to the target and an update strategy with a condition that says 'if PK exists, DD_UPDATE, else DD_INSERT' but by the time the third row from the source comes into play, the revenue and cost are nulls in the target.

Any suggestions?

TIA,

bitt

 

[Dagon]

It will be difficult do this in the way that you propose because you can't easily force Informatica to write to the targets in the correct order (insert then update).

The better solution is to use aggregation to get the data in the format you want before inserting it. You could do this in the source qualifier, assuming your source is a relational database e.g.

select pk, sum(revenue) as revenue,
sum(cost) as cost,
sum(percentage) as percentage
from table
group by pk

This will have the effect of flattening out the data into a single row. You could also use an aggregator transformation for the same purpose.