Print Page | Close Window

Removing duplicate values

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2046
Printed Date: 05 May 2024 at 7:14pm


Topic: Removing duplicate values
Posted By: garys
Subject: Removing duplicate values
Date Posted: 15 Jan 2008 at 2:45am
Hi all
 
I have a report that is generated with one record for the client that is a positive amount and then another record where that transaction was reversed showing as a negative amount. 
 
I want to be able to get crystal to remove such an occurence from the report based on the fact that the one cancels out the other.
 
Please could someone offer me some pointers here.
 
Tongue Gary



Replies:
Posted By: Lugh
Date Posted: 15 Jan 2008 at 4:16am
Unfortunately, the solution depends a great deal on the exact nature of the data.  Is there anything in the record of the reversal to tell that it is a reversal, and not simply a standard withdrawal?  Is there anything that references the original transaction, so that you can know for certain which transaction is being reversed?  Does the reversal always come immediately after the original transaction?


Posted By: garys
Date Posted: 15 Jan 2008 at 4:30am

The data would look something like this:

Member no.  claim no.     claim date      Process date       Amount
0132456        123456     2007/01/01      2007/02/01       1000.00
0132456        345689     2007/05/01      2007/06/01         500.00
0132456        123456     2007/01/01      2007/07/01      -1000.00
0123456        123456     2007/01/01      2007/07/01          850.00
 
The claim number and the member number are always the same, the amount reversed will always be the exact amount used the first time. 
It does not follow directly after as it seems to be date driven.
The only thing that tells me that it is a reversal is the negative amount.
 
i  hope this helps shed some more light


Posted By: Lugh
Date Posted: 15 Jan 2008 at 5:08am
OK, so the only negative amounts are reversals.  That helps.  Will the reversal and the original transaction always have the same claim number?  That should allow us to narrow it down, I think.  It will still be very tricky.

Ideally, I would like to solve this on the SQL side.  You are going to run into a lot of timing problems in Crystal.  Crystal processes each record, in order.  Then, it goes through and processes each record a second time, to allow records to interact.  Essentially, what you want Crystal to do is to go through and, if it encounters a negative amount, to back up and find and flag a previous record.

If you are able to do this in SQL, your query would look something like:


SELECT MemberNo, MyTable.ClaimNo, ClaimDate, ProcessDate, MyTable.Amount,
    (CASE WHEN (RevCheck.ClaimNo IS NOT NULL) OR (MyTable.Amount < 0)  THEN -1 ELSE 0 END) AS RevExist
FROM MyTable
LEFT JOIN
    (SELECT ClaimNo, Amount
     FROM MyTable
     WHERE Amount < 0) RevCheck
ON MyTable.ClaimNo = RevCheck.ClaimNo AND MyTable.Amount = ABS(RevCheck.Amount)


To walk you through a bit, the CASE statement creates a flag, that is 0 normally, but -1 when a reversal exists for that transaction.  The RevCheck subquery just looks for all the negative amounts.  The join matches up the negative amounts from RevCheck with records that have the same claim number, and the same amount.


If this kind of SQL is not available to you, then you're going to have to use a pretty brute-force method.  I strongly recommend using a forward-looking approach on each record, rather than a backwards-looking approach only on the negative records.  Essentially, create a SQL Expression that looks ahead for reversals.  If it finds one, flag the current record.  Also flag any records with negative amounts.

Once you have your flags in place, by either method, simply set a suppress condition on the flag.

Incidentally, this could be done much more simply if you are able to group by claim number, and sort within each group by amount rather than date.  But, it sounds like that likely isn't an option for you.



Posted By: garys
Date Posted: 15 Jan 2008 at 5:38am
Thank you Lugh.
 
I am going to give this a try and hope it works.
 
 


Posted By: garys
Date Posted: 17 Jan 2008 at 8:59pm
I could not get the above solution to work. I however did figure out another way to do it.
Every value that was reversed would equal to 0.00.  I then decided to use the SUM() function to get my result set to be 0.00 wherever a reversal had taken place.  The SQL is as follows:
 
SELECT "claim"."mem-num", "claim"."claim-num", "claim"."claim-date", "claim"."claimed" ,
SUM ("claim"."claimed")
 
FROM   "MA"."PUB"."claim" "claim"
 
WHERE  "claim"."mem-num" = ' 123456789'
 
GROUP BY "claim"."mem-num", "claim"."claim-num", "claim"."claim-date", "claim"."claimed"
 
Once i had the data, i then used the formula editor to show only values for the sum.claimed field when they are > 0.
 
 


Posted By: Lugh
Date Posted: 18 Jan 2008 at 4:56am
Er, except that it doesn't.  In your sample data, the sum of the values for the claim number is actually $850.  It goes +$1000, then -$1000, then +$850.  I presume that you do not want to suppress the $850 line.

Incidentally, what about my solution could you not get to work?  Was it just not giving the expected results?  I may be able to help correct any problems you have with it.





Print Page | Close Window