Print Page | Close Window

cross tab to eliminate duplicated rows?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=280
Printed Date: 03 May 2025 at 12:10am


Topic: cross tab to eliminate duplicated rows?
Posted By: aleplgr
Subject: cross tab to eliminate duplicated rows?
Date Posted: 06 Mar 2007 at 1:02am
Hi! I have a table with 3 columns: customer id, date and $ Value in the third column.
In this table I need to eliminate duplicated customer ids keeping the one with the highest $ value, to do this I'm using a crosstab, with customer id in rows and summarizing the Value with the Max criteria.. and then exporting this to an excel file and joining it with the original file to recover the Date column because the crosstab only allows one column.
I think there should be another way to do this, filtering the maximun value and keeping all the columns but can't find it... maybe using the Maximum operator in a formula but this does not eliminate rows, maybe it does but I can't find how Ouch


Replies:
Posted By: BrianBischof
Date Posted: 06 Mar 2007 at 9:00am
Do you know anything about SQL? This can be done using a Command object and using SQL as your datasource. The pseudo code is:
 
SELECT CustomerId, Date, Max(field)
FROM table
GROUP BY CustomerId, Date


If you don't know SQL, then I would create two groups by the Customer Id and Date and then only show one of the group headers and not the detail rows. That would eliminate all the extra rows from being displayed.


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>


Print Page | Close Window