Duplication Problem

[kchaudhry]

I am using CR 8.5 with SQL 2000 backend. I am facing a problem of getting duplicate records. I have "select distinct records" checked. Basically my joins I think are causing the problem.

we have a participant table with 2 rows.

id=1, name=smith
id=2, name=chaudhry

we have a formula table with 4 rows

id=1, formula="Productivity Commission/Total"
id=1, formula="Productivity Commission/Team Pool"
id=2, formula="Productivity Commission/Total"
id=2, formula="Productivity Commission/Team Pool"

we're joining both these tables on ID (equal join)

so we end up with 4 rows..the participant info gets duplicated.

participant.id=1, participant.name=smith, formula.id=1,
formula.formula="Productivity Commission/Total"
participant.id=1, participant.name=smith, formula.id=1,
formula.formula="Productivity Commission/Team Pool"
participant.id=1, participant.name=chaudhry, formula.id=2,
formula.formula="Productivity Commission/Total"
participant.id=1, participant.name=chaudhry, formula.id=2,
formula.formula="Productivity Commission/Team Pool"

Is it possible to get around this problem using the record selection? Or is there any other way of resolving this problem?

Thanks in Advance.

Kchaudhry

 

[synapsevampire]

What is it that you want returned?

You've demonstrated what the query returns, and your assessment is accurate as to what should get returned, but you didn't show what you want.

-k

 

[kchaudhry]

Currently I am getting two records for each ID. I would like to get only one record for each ID.

Kchaudhry

 

[kchaudhry]

Ok maybe I was not able to explain the problem clearly so I am going to try again.

Here is my record selection formula:

{participant.position} in ["OAKWOOD AC", "OAKWOOD AE", "OCH AC", "OCH AE"]
and
(left({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4))
=
(left({plan_manager.set_period_str},4))
and
(mid({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2))
=
(mid({plan_manager.set_period_str},5,2))
or
{Participant_Move_in_Trans.Six_Mo_Contract_Com_Amt} > 0

Now whats happening is that I am getting duplicate records for all the AC's. The results for AE's are showing up fine.
How can I fix this?

Thanks,

Kchaudhry

 

[kchaudhry]

Any ideas as to how I can solve this problem?

Any help would be appreciated.

Kchaudhry

 

[lbass]

Your example does not show any duplicate records, just duplicate fields. For each person there are two records, because there are two {formula.formula} results, i.e, your data looks like this:

partID partName formulaID formulaformula
1 Smith 1 ProdCom/Total
1 Smith 1 ProdCom/TeamPool
2 Chaudry 2 ProdCom/Total
2 Chaudry 2 ProdCom/TeamPool

You could group on {Participant.ID} and then create two separate formulas:

//{@prodcomtotal}:
if {formula.formula} = "Productivity Commission/Total" then {formula.formula}

//{@prodcomteampool}:
if {formula.formula} = "Productivity Commission/Team Pool" then {formula.formula}

Then insert a maximum on each of these, drag the other fields to the group footer and then suppress the details.

-LB

 

[kchaudhry]

I am sorry, I am having a really bad day explaining what my problem is. Ok I will give it another try. As mentioned in the post above, I am getting duplicate records in the details section for AC type employees. On the other hand I am getting correct records for the AE type employees in the details sections. I have to show the details section. What I am trying to do is to remove these duplicates from the details section. Hope this clarifies my problem. If not then please let me know and I will try to explain it again.

Thanks,



Kchaudhry

 

[synapsevampire]

You don't seem to understand this, they're not duplicates.

I guess adding this cheat to your record selection formula might suffice:

and
(
{table.formula} = "Productivity Commission/Total"
)

-k

 

[lbass]

Please show a sample of the results you are getting like I laid out in my post.

-LB

 

[synapsevampire]

BTW, if you don't like that, then try grouping by the ID and adding the following to the record selection formula->group

{formula.formula} = max({formula.formula},{participant.id})

-k

 

[kchaudhry]

Ok, here is where I have posted a sample of my report.

http://www.techqt.com/commissionsummary.pdf

If you look at the sample you will see for SPID = 6027 the detail section is showing all the records twice. Maybe this will help explain my problem. On the other hand if you look at SPID = 6010 it is showing the records only once. I am confused why this is happening.

Thanks for all your help.

Kchaudhry

 

[lbass]

Okay, so you do have duplicates. If these are all detail records, the first thing I would try is field formatting. For each field, go to format->field->common->suppress if duplicated.

Why is this happening? Maybe the name field is entered two different ways in the first table, but with one ID, causing duplication of records. Or any second entry from the first table could cause this, I think.

-LB

 

[kchaudhry]

LB,

I cant go to each field and supress if duplicated as some of them do actually repeat. For example the Proj # can be the same even if it is not a duplicate. One thing which I have found out is that currently I have an equal join from Participant table to Formula table linked on the "ID" field. If I delete this link then the duplicate problem is gone. The prbolem then is that the report is extremely slow.

I am still lost :-(

Kchaudhry

 

[lbass]

Isn't that the ONLY link between the two tables?--I don't see how you could delete it and still have the report run.

But, in any case, restore the link. I wasn't thinking clearly about the suppress if duplicated issue. You could try this though. Create a formula {@concat} which concatenates all of your fields. Make your life easier and use "&" instead of "+" and you won't have to adjust for data types at all. Then go to format field->common->suppress if duplicated ->x+2 and enter:

{@concat} = previous({@concat})

This should work.

-LB

 

[kchaudhry]

I can try suppressing the details section but I dont think that will solve my problem. The summaries will still reflect the duplicate values. I was thinking of making changes in the record selection.

Here is what I have for now.

(({participant.position} in ["OAKWOOD AE", "OCH AE"] and {formula.formula} = "Productivity Commission/Total&quot
or
({participant.position} in ["OAKWOOD AC", "OCH AC"] and {formula.formula} = "Productivity Commission/Team Pool&quot)
and
(left({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4))
=
(left({plan_manager.set_period_str},4))
and
(mid({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2))
=
(mid({plan_manager.set_period_str},5,2))
or
{Participant_Move_in_Trans.Six_Mo_Contract_Com_Amt} > 0

Do you think this will be the correct approach?

Thanks.

Kchaudhry

 

[lbass]

What summaries? That's the first you've mentioned them, I think. With duplicate records, you could use running totals to count/sum only non-duplicated records.

Your revised record selection formula might fix the problem, so you might try that first. Not sure I can add anything more of help.

-LB

 

[kchaudhry]

LB,

I appreciate all your help. I think the record selection has solved my problem.

Thanks once again.

Kchaudhry