How can we eliminate cartesian records???

[srvu]

i have a table T1(master) where i agg sum(values) based on enrollid and accumid.
values are
enrollid accumid value
123 212 200
123 213 300
123 214 400

Now i have an other table T2(detail) where i need to join with T1
i need to use master outer join

T2 is as follows

Enrollid B1 B2 B3
123 300 0 0

I have agg the table T2 sum(B1),sum(B2), sum(B3) on Enrollid as key port
then above is the output


Now i need to join T1 and T2 using Master outer join (joiner) and add values of amounts as below
ID Amount1 Amount2 Amount3
123,B1(300+200(value at 212),B2(0)+300
(value at213), B3+
(value at 214)
Below should be the output

Enrollid Amt1 Amt2 Amt3
123 500 300 400 But i am getting out put as


123 500 0 0
123 0 300 0
123 0 0 400


Kindly help me how can we get out put as mentioned above as one single record instead of 3 diff records.

 

[blom0344]

Be sure to aggregate prior to joining, which in your case the cardinality between the incoming dataflow is 1:1.

But even without this step, it is just one additional to aggregator to bring everything back to one row just before writing to the target.

I think I have asked this earlier, did you check what an aggregator transformation can do for you?

Ties Blom

 

[srvu]

I have used Agg to Sum amt fields based on enrollid and accumid but the amts are not getting added

Suppose there are records as below

Enrollid Accumid Amts
123 212 200
123 212 200
123 213 300

I should get output as

123 212 400
123 213 300

but i am getting out put as

123 212 200
123 213 300


i have used Agg t/r and used sum() and used enrollid and accumid as group by ports

 

[blom0344]

The distinct effect will be achieved by using max() instead of sum() to get correct amts for each group.

Of course , you have to make sure that you REALLY do not need sum() anywhere in this transformation!!

Ties Blom

 

[srvu]

i want to simplify my Question

enrollid accumid accumvalue1 accumvalue2 accumvalue3
123 212 200 100 0
123 212 200 0 100
123 213 0 100 200
123 214 0 0 300

i should get the output as

123 212 400 100 100
123 213 0 100 200
123 214 0 0 300


i have used sum(accumvalue1), sum(accumvalue2), sum(accumvalue3) group by enrollid and acumid

but i am getting agg value but not the summed amounts grp by accumid's

The output i am getting is as follows

123 212 200 100 0
123 213 0 100 200
123 214 0 0 300

 

[blom0344]

The aggregator transformation should be defined as:

1. Specify grouped ports (in/out)
2. locate incoming ports of accumvalue 1/2/3
3. specify outgoing ports only as :
sum(accumvalue1)
sum(accumvalue2)
sum(accumvalue3)

In that order.

Your example indicates that Powercenter passes the last or first row which would indicate that aggregate is not properly defined.

Ties Blom

 

[srvu]

i have defined my ports as you mentioned still its giving same result.

i have group by on enrollid and accumid

can i have a sum condition like this
sum(accumvalue1,count(accumid)>1) , if we use count on accumid, will it count based on groupby ports(enrollid, accumid) or will it group entire accumid's totals??

 

[blom0344]

I do not think that the syntax you propose will pass, nor will it have the proper result. The Designer help is pretty extensive on transformations. I have used this about a zillion times in the past and it never failed to work as it should. Frankly, I am at a loss what could be wrong, this is all pretty basic stuff..

Ties Blom