×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

How can we eliminate cartesian records???

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close