×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
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??

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

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!