## How can we eliminate cartesian records???

## How can we eliminate cartesian records???

(OP)

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.

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.

## RE: How can we eliminate cartesian records???

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

## RE: How can we eliminate cartesian records???

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

## RE: How can we eliminate cartesian records???

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

Ties Blom

## RE: How can we eliminate cartesian records???

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

## RE: How can we eliminate cartesian records???

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

## RE: How can we eliminate cartesian records???

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

## RE: How can we eliminate cartesian records???

Ties Blom