Find common rows

[21128]

Joining 3 different tables i have the following result in CR

Name ID Status s_date e_date branch bill pay

Dell 1 Pending 12/19/2011 12/31/2011 2 62 40
Dell 2 Search 12/19/2011 12/31/2011 2 46 30
Dell 3 Search 12/19/2011 12/31/2011 2 31 20

Dell 4 Pending 12/19/2011 12/31/2011 3 62 40
Dell 5 Search 12/19/2011 12/23/2011 3 62 40
Dell 6 Search 12/19/2011 12/31/2011 3 46 30
Dell 7 Search 12/19/2011 12/31/2011 3 31 20

ID 1 is equal to ID 4
ID 2 is equal to ID 6
ID 3 is equal to ID 7

How do i add common bill?
I need the following result
ID 1 + ID 2 + ID 3 (62 + 46 + 31)= 139

 

[lbass]

What is the ID field--is it the special field "record number" or is a running total indicating some sequence? Or is it an actual database field?

One approach would be to create a formula {@concat} like this:

{table.name}&" "&{table.status}&" "&{table.edate}&" "&{table.sdate}

Insert a group on this and then insert a running total that does a sum of {table.bill}, evaluate on change of group: {@concat}, reset never.

This assumes that these values would be unique. Ideally you would be grouping on a unique ID field that would show that these rows are really only one transaction.

If the "ID" field you show above is really a database field, you could create a formula that clusters them and then group on the formula instead of using the concatenation formula like this:

select ID
case 1,4 : 1
case 2,6 : 2
case 3,7 : 3
default : 4

...assuming ID is a number field.

-LB