| Author |
Message |
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
  
Topic: report regarding duplicating records
Posted: 30 May 2009 at 12:13am |
Hi all,
I have a report where there are some particular records as follows:
OrderID BookTitle Campus Copy Unit Total_Price Receipt_Price
WB223 XXXXXX w 1 46.50 46.50
WB223 XXXXXX X 1 46.50 46.50
WB223 XXXXXX y 8 46.50 372.00
WB223 XXXXXX z 7 46.50 352.50
Book title is same with copies allocated to different campus
Table1 (order id)
IRN ID
6786567 WB223
Table 2 (LINK table)
IRN ILILNK
6874311 NULL 6786567 NULL
6874337 NULL 6786567 NULL
Table 3 (Price table)
IRN Price
6786567 2042 46.50 46.50 17 NONE 0 0.00 NULL NULL NULL 1 17
6874311 2042 42.27 42.27 3 NONE 0 0.00 0 0.00 NULL 1 NULL
6874337 2042 45.45 45.45 10 NONE 0 0.00 0 0.00 NULL 1 NULL
We are here interested in the above three records.
When the library ordered these items, the price is 46.50. Then books arrived with two invoices where there are two new different prices. Normally you would have one invoice, this order has two invoices.
When I want to display the receipt prices (required by customer): copies x invoice prices, the records in the report are doubled:
Receitp_price
WB223 XXXXXX w 1 46.50 46.50 42.27
WB223 XXXXXX w 1 46.50 46.50 42.27
WB223 XXXXXX x 1 46.50 46.50 45.45
WB223 XXXXXX x 1 46.50 46.50 45.45
WB223 XXXXXX y 8 46.50 372.00 338.16
WB223 XXXXXX y 8 46.50 372.00 338.16
WB223 XXXXXX z 7 46.50 352.50 318.15
WB223 XXXXXX z 7 46.50 352.50 318.15
Is there any better way to handle this problem? Could I conditionally suppress the duplicated record without affecting other normal records? Could anyone please advise? Thanks in advance.
John
Edited by johnwsun - 31 May 2009 at 5:48am
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 01 Jun 2009 at 6:22am |
If you are getting the records from a stored proc of view, use SELECT DISTINCT. Joining straight to the tables, my first try would be for each item in the row to Format Text/Common tab/Suppress If Duplicated. Then on the row, Section Expert/Suppress Blank Section.
Or, this just came to mind, in Section Expert/Suppress/X-2 use the Next function and check if the values of the columns are the same, and if so, suppress the row. I haven't used the Next function, but I am sure that the syntax is not too difficult.
HTH
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Jun 2009 at 6:58am |
Another option for suppression would be to create a formula field to combine the fields into a single text string (something like ORDERID + IRN# + title), group on that and use this header to display the record data only one time and suppress the details altogether (which would be the 2 records that have the same combo string).
As always this will not resolve any counting or summing issues that are picking up duplicates.
Lockwelle's Stored Proc or view idea would be much better to address removing the duplicates altogether.
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 01 Jun 2009 at 2:11pm |
Hi lockwelle, DBlank,
I have used the previous function trying to suppress the duplicating records in the Section Expert/Suppress/X-2 with condictional statement:
if the campus code = previous(campus.code) and
order.id = previous(order.id). after duplicating removed, but the receipt price column only display one price 42.27 for one item and 42.27 * 7 or 8 copies for the other two campus. 45.45 price was lost? Obviously, the repport doesn't know which is which for these two different prices?
John
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Jun 2009 at 2:40pm |
1. YOu have to have your record sort in a way that allows for using of next and previous.
2. You have to use other logic in your supprss statement. If you do not it would take all 6 records and suppress down to 1.
Assumin yuo sort correctly you proabaly can just use the Price IRN field to suppress on it is uniquely duplicated on these rows, correct?...
previous(sales.irn)=sales.irn
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 01 Jun 2009 at 4:17pm |
Hi DBlank,
I cannot suppress the records down to 1 because there are reocrds for different campus; there have to be four campus with copies of books allocated ( they were ordered by one order). I might have to use 'next' to suppress( but in a sense, it's similar as 'previous'). I need receipt price, when I suppress, there is only one receipt price for all four campus--42.27, the other 45.45 was not displayed as it's suppress. Anyway I have another look into it , and let you know.
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 01 Jun 2009 at 6:07pm |
Hi DBlank, Lockwelle,
Can I use subreprot or SQL expression in the receipt price column?
Please advise, thanks a lot.
J
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 02 Jun 2009 at 6:23am |
Obviously, I don't know the data, but it would seem that part of the problem is that the report doesn't know which order a book came from, and since the data looks the same it is picking the first one.
If you can tell which invoice a book came from, or can create rules so that multiple invoiced items are grouped as far as price, then I would suggest creating a stored proc. From the information provided, and the desire to show allocations to the campuses, yes, the report is getting confused and doesn't know where the books went.
Unfortunately, this is one of the problems that can arise when we join directly to the tables, we get duplicated data that is hard for the report parse. As hilfy's tagline says, the computer does what we told to, not what we meant.
A stored proc or view could correct this by allowing you to retrieve the data in such a way as to be correct.
I am sure that you could use a subreport, but I am not sure how, since you need to allocate to a campus, and there might be a mixed allocation as well, which one would think, you would want the duplicate records but with different receipt amounts.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Jun 2009 at 2:49pm |
Hey John,
Don't know your data well enoughg but I think you could probably use a few views to get it down to non duplicted rows.
As for above I wasn't suggesting to limit it down to 1 row via supression, I was warning that your existing process might result in that. I think if you sort on these fields in this order:
OrderID (primary)
BookTitle (secondary)
Campus (tertiary)
Price.IRN (quaternary)
and then use either previous or next functions (you are correct that it is basically the same thing here) comparing the price.IRN field it should suppress only your duplicate rows.
suppress detail row with formula: previous(price.irn)=price.irn
|
| IP Logged |
|
Duncan3
Newbie
Joined: 10 Jun 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 10 Jun 2009 at 2:48pm |
I am having a similar problem
Sub location ID Order ID Customer ID Date
2west 4421 9921 3/2/09
4 east 4424 9921 3/2/09
Hem 4436 9921 3/2/09
What I need is
- to only select the first order id for each customer (I don’t need to see the other data)
- I need to group bysub-location and totals for each sub-location id of the customers first orders
Can anyone HELP?
|
| IP Logged |
|
|
|
