Eliminating Duplicates from different rows
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14771
Printed Date: 29 Apr 2024 at 10:37pm
Topic: Eliminating Duplicates from different rows
Posted By: moontide
Subject: Eliminating Duplicates from different rows
Date Posted: 25 Oct 2011 at 3:26am
|
I have a report with a summary value that is repeating in row 6 and then it repeats in row 9 and I used the 'suppress if duplicate' under 'format field' option but it didnt work. So I used a formula instead.
if {table.amount} = next({table.amount}) then 0 else {table.amount}
The formula above only suppresses if two consecutive rows are showing duplicates, but does not if duplicates are occuring in a different row. Below is what is showing from the formula. It's a numeric variable.
Amount
1. 2789.89
2. 89233.89
3. 7892.89
4. 0
5. 89343.78
5. 0
6. 627322.56
7. 23232.36
8. 78292.35
9. 627322.56
The rows in bold is the problem. Row 9 should be suppressed because its duplicating row6, but the above formula is not recognizing it. So what other alternatives are possible for this? Please...any ideas?
|
Replies:
Posted By: FrnhtGLI
Date Posted: 25 Oct 2011 at 3:55am
You can try creating a string variable that attaches each amount to it. Something like:
whileprintingrecords;
global stringvar sAmount:=sAmount & ',' & {table.amountfield};
This field will have to be placed on the detail line and you can suppress it so it doesn't show.
Then try setting the detail line to suppress if {table.amountfield} in global stringvar sAmount.
The suppression part might not work properly though because it may evaluate the amount field before it does the suppression and then every detail line will be suppressed.
If that is the case, you could create a new formula field to display the amount field. Something like:
if {table.amountfield} in global stringvar sAmount
then ""
else {table.amountfield}
If you use this scenario, instead of setting the string variable field to whileprintingrecords set it to evaluateafter({@AmountField}).
Hope that helps.
-------------
|< /\ '][' ( )
|
Posted By: moontide
Date Posted: 25 Oct 2011 at 5:03am
|
Thanks for your suggestion but I dont understand why I have to use 'stringvar'?...the amount field is a numeric...and even if I use the stringvar I have to convert the rest of the {table.amount} using 'totext'...which I did and where do I use the evaluateafter(table.amount}...I didnt clearly get what you meant by that.
|
Posted By: FrnhtGLI
Date Posted: 25 Oct 2011 at 6:17am
Well, with a numbervar, it's not going to allow you to keep a list, which is basically what the stringvar will do. Like you said, you will have to convert to text.
the evaluateafter function will be used in the first formula:
evaluateafter({@AmountField});
//instead of whileprintingrecords//
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
The {@AmountField} will be the new formula you created:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
Then you can suppress the detail sections when {@AmountField}=""
-------------
|< /\ '][' ( )
|
Posted By: moontide
Date Posted: 25 Oct 2011 at 7:30am
|
Ok. I created the new formula @amountfield and applied the mailto:evaluateafter%7b@amountfield%7d - 'evaluateafter( mailto:%7b@amountfield%7d%29; - {@amountfield});' in the string variable but I get an error which says 'A formula cannot refer to itself, either directly or indirectly'.
|
Posted By: FrnhtGLI
Date Posted: 25 Oct 2011 at 8:03am
You need two formulas: You can name them whatever, but for the sake of this post, I will name them AmountField and StringVarField.
Create them both before adding anything to them. Then, for the StringVarField, put in the first formula:
evaluateafter({@AmountField});
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
Then add the next formula in AmountField:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
-------------
|< /\ '][' ( )
|
Posted By: moontide
Date Posted: 25 Oct 2011 at 8:26am
|
Got it. It's working now. Thanks alot buddy!...appreciate your time :) !
|
Posted By: moontide
Date Posted: 25 Oct 2011 at 8:30am
|
One last question. There are other fields in the report that are also showing the duplicates as the amountfield was showing, do I have to use the same method for all the fields because now the amounbt field is working fine now but the other adjoining fields are showing the duplicates. Is there a formula that I can apply for all the fields in the section expert?.
|
Posted By: FrnhtGLI
Date Posted: 25 Oct 2011 at 8:38am
Are the adjoining fields on the same detail line as the Amount field? Are the duplicate adjoining fields related to the duplicated Amount fields?
I would think that if the fields are on the same detail line and they are related, that they would be suppressed if you go to the Section Expert and set the suppression of the detail line based off the "" value of the @AmountField formula.
-------------
|< /\ '][' ( )
|
Posted By: moontide
Date Posted: 25 Oct 2011 at 8:59am
|
Its all working fine now. Thanks again!
|
Posted By: FrnhtGLI
Date Posted: 26 Oct 2011 at 2:47am
So is the amount field displaying on the line as empty with these fields populated?
-------------
|< /\ '][' ( )
|
Posted By: moontide
Date Posted: 26 Oct 2011 at 8:49am
|
yes it was, but i know why, there was another condition that I put earlier in the detail section which was causing it, so I removed it. As for now, the results are showing exactly what i wanted.
|
Posted By: tbgrewal
Date Posted: 03 Jan 2013 at 6:18am
|
Thanks for the tip guys but I'm afraid it isn't working for me. I created 2 formula fields, one called 'Physician' and the other 'ReferringProvider'.
I attached the following formula to the first one:
global stringvar sPhysician:= sPhysician & {GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality} & ","
To the second I attached the following:
EvaluateAfter ( mailto:%7b@Physician - {@Physician });
if totext({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) in global stringvar sPhysician
then ""
else ({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality})
The idea is that if sPhysician contains the Physician already, display a blank line otherwise display the physicin's name etc. Am I using the correct syntax or call to sort i.e. "in". Does this make senese?
|
Posted By: lockwelle
Date Posted: 03 Jan 2013 at 8:34am
|
I would have tried: EvaluateAfter ( mailto:%7b@Physician - {@Physician });
if instr(sPhysician, totext({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) > 0 then ""
else ({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) or: EvaluateAfter ( mailto:%7b@Physician - {@Physician }); global stringvar sPhysician
if totext({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) in sPhysician
then ""
else ({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) also, I am assuming that GetDataFromEDIClaims.ReferringProvider} is a string...so you really don'ty need totext, like everywhre else that you use this string. HTH
|
Posted By: tbgrewal
Date Posted: 03 Jan 2013 at 12:12pm
|
Thanks for your suggestion. I tried what ou suggested and nothing gets displayed. It's as if the evaluation gets triggered after the sPhysician string has been completely created. It's just weird. I have to get this thing to work.
|
Posted By: tbgrewal
Date Posted: 03 Jan 2013 at 12:15pm
|
Oh and it won't let me use:
if InStr (sPhysician, {GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) > 0
but will allow:
if InStr ( mailto:%7b@Physician - {@Physician }, {GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) > 0
even though the string has been defined as being global. I even tried:
EvaluateAfter ( mailto:%7b@Physician - {@Physician });
global stringvar sPhysician;
sPhysician = ( mailto:%7b@Physician - {@Physician });
if InStr sPhysician, {GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality}) > 0
then ""
else ({GetDataFromEDIClaims.ReferringProvider} & " - " & {GetDataFromEDIClaims.Modality})
|
Posted By: tbgrewal
Date Posted: 07 Jan 2013 at 8:07am
|
Hi there; how did you get this to work? I have tried implementing this but it doesn't work for me. are you able to go the forum page to view my responses? Thanks.
You need two formulas: You can name them whatever, but for the sake of this post, I will name them AmountField and StringVarField.
Create them both before adding anything to them. Then, for the StringVarField, put in the first formula:
evaluateafter({@AmountField});
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
Then add the next formula in AmountField:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
|
Posted By: tbgrewal
Date Posted: 07 Jan 2013 at 1:50pm
|
Hi; may I ask how you got this to work? Thanks.
You need two formulas: You can name them whatever, but for the sake of this post, I will name them AmountField and StringVarField.
Create them both before adding anything to them. Then, for the StringVarField, put in the first formula:
evaluateafter({@AmountField});
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
Then add the next formula in AmountField:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
|
Posted By: lockwelle
Date Posted: 08 Jan 2013 at 9:05am
|
I never tried...don't have the data etc. I just replied with the syntax that is more correct. I don't use 'in' except when dealing with arrays, so totext(xxx + '-' + yyy) in zzz didn't make sense to me. if xxx or yyy were numbers, I believe that this would fail (can't add numbers like strings. Also, typically totext adds decimals and commas unless told not to (at least for numbers, dates are different) so I replied that instr(zzz, xxx + '-' + yyy) > 0 should work better...it's been my experience that if your code doesn't work, you look for possible solutions, which is someone else's code. Alas, we're not always correct, or there is some other twist that we are unaware of. as for debugging, have you tried: after evaluate (sPhysician) global stringvar sPhysician; sPhysician just to check that the values that you think are in the string are actually there? debugging CR can be tough as it is a black box. You are pretty sure about what goes in, and you are positive about what comes out, but you can't see what CR is doing or how it is working, so sometimes you have figure out how to 'see' what CR is doing and compare that to what you thought CR was doing. HTH
|
Posted By: tbgrewal
Date Posted: 01 Feb 2013 at 1:02pm
|
I did get this to work in the end. Many thanks for everyone's responses. I basically built up the first variable (@Physician) as follows:
If (Not IsNull({GetDataFromEDIClaims.ReferringProvider}) And ({GetDataFromEDIClaims.ReferringProvider} <> "")) Then
Global StringVar sPhysician:= sPhysician + {GetDataFromEDIClaims.ReferringProvider} + " - " + {GetDataFromEDIClaims.Modality} + ",";
and then used the follwing code in the second and final variable (@ReferringSource):
EvaluateAfter ( mailto:%7b@Physician - {@Physician });
//local variables:
Local NumberVar Pos1 = 0;
Local NumberVar Pos2 = 0;
//find 1st position of Physician's Name in the string just constructed in @Physician:
If (Not IsNull({GetDataFromEDIClaims.ReferringProvider}) And ({GetDataFromEDIClaims.ReferringProvider} <> "")) Then
Pos1:= InStr( mailto:%7b@Physician - {@Physician }, {GetDataFromEDIClaims.ReferringProvider} + " - " + {GetDataFromEDIClaims.Modality});
//find the next occurence of the Physician's Name:
If (Not IsNull({GetDataFromEDIClaims.ReferringProvider}) And ({GetDataFromEDIClaims.ReferringProvider} <> "")) Then
Pos2:= InStr(Pos1 + 1, mailto:%7b@Physician - {@Physician }, {GetDataFromEDIClaims.ReferringProvider} + " - " + {GetDataFromEDIClaims.Modality});
//Pos1 and Pos2 > 0? (occurs if Physician's name is found 2nd time or more - do not display the Physician's name again:
If (Pos1 > 0 And Pos2 > 0) Then
""
//otherwise display Physician's name:
Else If ({GetDataFromEDIClaims.ReferringProvider} <> "") Then
{GetDataFromEDIClaims.ReferringProvider} + " - " + {GetDataFromEDIClaims.Modality};
|
|