Limiting a Duplicate Record

[rbh123456789]

CR 11.5

Hello,

I have a crosstab which counts the number of records with a value of '3' for each month (3 represents a string in another table)

If there are 3 records in a month with a value of 3, then the CT will display '3' for the month.

Is there a way to limit Crystal to only pull a maximum of 1 record, where or not there is multiple values of '3'?

//This is the summarizied field in the CT, which is a SUM
if {RPT_CASELOAD_MONTH.PreviousDate} < {?date_start} then 0 else {@CaseNew}

//@CaseNew
if {RPT_CASELOAD_MONTH.PreviousStatus} = [3] then 1

 

[lbass]

This is unclear. What is the problem you are trying to address? You are using '3' for both counts and values in the formula, and this too is confusing. You mention 'duplicates' in the thread title, but don't reference it again. What exactly is duplicating?

How is the crosstab set up? What fields are you using for column and row?

-LB

 

[Madawc]

If you want to count just one for multiple occurences of the same value, use 'Distinct Count'.

If you want the crosstab to group by values like [3] but show something else, then use a formula field. ToWord might be useful.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[rbh123456789]

Sorry if this problem is not clear.

1. There is a table in our db named 'Status'. The Status table has 5 entries which are: Open, Closed, Initial, Pending, Final. The corresponding unique ID for each Status is: 1 = Open, 2 = Closed, 3 = Initial, 4 = Pending, 5 = Final.

2. The Status table is linked to the 'StatusHistoryTable', which keeps a log of every time a cases status is changed.
The StatusHistoryTable has a date column which shows when the cases status was changed.

3. The CT uses the dates (grouped by month) found in the StatusHistoryTable to created monthy numbers.

4. The CT has 5 rows, which is broken down by the Status.

The problem is that there is an entry in the CaseStatusHistory table which has two 'Initial' entries.

This was a mistake by the user (as there should only ever be one 'Initial' entry per case) who inputted in the information into the db. I do not want to delete the extra record, I just want to ignore one of them.

I cannot make the question any more clear.

 

[lbass]

You could try changing your formula to the following, assuming caseID is a number:

//@CaseNew
if {RPT_CASELOAD_MONTH.PreviousStatus} = [3] then {table.caseID} else
tonumber({@null})

...where {@null} is a new formula that you open and close without entering anything.

Change your first formula to:

if {RPT_CASELOAD_MONTH.PreviousDate} < {?date_start} then tonumber({@null}) else
{@CaseNew}

Then instead of a sum, use a distinctcount on the formula.

-LB

 

[rbh123456789]

Hello lbass,

The caseid is in the format of '2008-1234-N'.

Will this pose a problem?

 

[lbass]

Remove the tonumber() from each formula, and you should be all set.

-LB

 

[rbh123456789]

Thanks. My report is working but that extra unwanted record is still showing up.

I checked the entry in teh db and it looks like the record is not a 'complete' duplicate, there is a date stamp which has a different time...

 

[lbass]

If you are using a distinctcount, then there can be only one record returned in the crosstab per caseID. So is the issue that there is a result in more than one month per status?

-LB

 

[rbh123456789]

Thanks, see the attachment.
The issue are the 2 records in the red square. The fact that there are 2 records in the Initial status [3] was an error by the user, which is throwing off my count.

http://i595.photobucket.com/albums/tt37/hammondrob/untitled-1.jpg

 

[lbass]

I odn't know what you are showing me--an Excel spreadsheet? I thought you were using a crosstab in CR? You didn't answer my question. Why don't you remove the incorrect record from the database?

-LB

 

[rbh123456789]

No, that is not an excel spread sheet, that is a query window SQL Server

-I am using a cross tab in CR, i was just showing you the record that i want ignored.

-We do not delete case historys from our database for audit reasons

I was just hoping there would be a way for CR to count only one of the two Initial records.

 

[lbass]

You could potentially set up a SQL expression to select one of them, but how you do know which one you really want? The most recent date? Try something like {%maxprev}:

(
select max(PreviousDate)
from RPT_CASELOAD_MONTH A
where A.CaseID = RPT_CASELOAD_MONTH.CaseID
)

Not sure the appropriate punctuation for your datasource. Then in your record selection formula use:

{RPT_CASELOAD_MONTH.PreviousDate} = {%maxprev}

-LB

 

[rbh123456789]

Thanks for your patience lbass.

Now, i do already have a record selection statement:

{RPT_CASELOAD_MONTH.PreviousDate}<= {?date_end}

Should i add your statement as well?

 

[lbass]

Well, I forgot about that--the issue is that the SQL expression goes right to the database and doesn't honor the record selection formula, which means you would have to build your date criterion into the expression--and SQL expressions don't take parameters. So instead, you would have to create the subquery in a command where you CAN add the parameter. Set it up like this:

Select max(PreviousDate), RPT_CASELOAD_MONTH.CaseID
from RPT_CASELOAD_MONTH
where RPT_CASELOAD_MONTH.PreviousDate <= {?Date}
Group By RPT_CASELOAD_MONTH.CaseID

You should create the parameter within the command, and then you can use it there and in the main report. You would then link the command to the Rpt_caseload_month table on the case ID and link the expression to the the previous date field.

-LB

 

[rbh123456789]

Hello,

Before reading your last post i added a WHERE clause to my View on the SQL Server. I specifically crafted the clause to ignore the one row.

Now everything is working fine.

Thank you very much for all your help.

 

[piovesan]

Hi;
I am using Crystal Reports XI and I have a basic report that lists land purchased within a selected date range. One of the fields I want to display is a location (address). But there may be more than one address on the parcel. I need only to display one of those addresses. The address table is(called LND_CIVIC_ADDRESS) and besides the civic number field (Civ_Num), it also contains a unique ID called CIV_ID. But I fiqured where there was more than one address per land parcel, I would just use the Maximum Civ_Num. So I have created a formula called @maxcivic that looks like this:
if not isnull({LND_CIVIC_ADDRESS.CIV_ID}) then CStr (maximum({LND_CIVIC_ADDRESS.CIV_NUM}),0 ,"" )& ProperCase ( " " &{LND_CIVIC_ADDRESS.STR_NAME} & " " & {LND_CIVIC_ADDRESS.STR_TYPE} & " " & {LND_CIVIC_ADDRESS.GSA_NAME})

However, what this seems to do, is it finds the maximum civic number in ALL the records, and just uses that for all streets..... for example, if it is supposed to be:
12 Apple St
48 Pear St
200 Orange St

with the way I have it set up, it turns out like this:
200 Apple St
200 Pear St
200 Orange St

It does not seem to matter if I put this formula in the details section, or if I create a group section........it still turns out this way.......What do I need to change??
Thanks!

 

[piovesan]

oops!! soory, this is supposed to be a new question....... sorry! I will re-post this