| Author |
Message |
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
   Topic: Group header with date issue
Posted: 27 May 2015 at 5:55am |
|
I have a report with 8 fields and ~50,000 rows. Some of the rows are exact duplicates. I created a group of each of the 8 fields and moved them into 1 row, then hid the details. This resulted in ~4,000 rows. HOWEVER, the group row is messing up the dates. The date field is the same for each of the detail rows in the group e.g. 5/27/15. But in the group row, it shows up as 5/24/15. In all cases the date in the group row is 2-4 days prior from the date in the component detail rows. I would export to Excel and then “remove duplicates” but it’s too big a file. I am sure that is FAR FROM the best way to handle it but with my limited point and click skills, it seemed to be a quick solution. Thanks for any advice!!
|
|
Amanda from the Mid-Atlantic
|
 IP Logged |
|
|
Erik
Groupie
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
|
Posted: 27 May 2015 at 6:15am |
|
Instead of Grouping the like values, I would go back and examine the way
you have your data linked to try and prevent the duplicates from coming
into the report. It sounds like maybe you have an outer join where you
should have an inner join.
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 27 May 2015 at 6:24am |
|
Erik, I think you are right about the joins. I have about 7 tables which are joined in various ways to each other and find selecting the join type very daunting under those circumstances. Any advice would be helpful, when joining so many tables. Thanks!
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
Erik
Groupie
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
|
Posted: 27 May 2015 at 6:37am |
|
Imagine two circles that overlap with each other. Each of them represents a pool of data. Where the circles overlap (have matching values) is where an inner join will return the data for.
If you want data outside the overlap then you'd need an outer join. If you want only the outer data on the left side, then left join. If only outer data on the right side, then right join. If outer data on both sides, then outer join.
In your case, I would try changing all the joins to inner join to prevent extraneous data from coming in.
The potential concern here is that you could end up losing data that you might want in your report. If you have a common value that lives in one table but not the other, then an inner join will omit all data from the table where the common value does not live.
A practical example of this in action is in our call tracking system. We have a field called "Root Cause" which is not populated until a ticket is closed. If I did an inner join on the table that has those values, I would only get rows in my report that had a Root Cause value populated, which would omit any unresolved tickets from my report. I deliberately use an outer join on that link so that I get blank Root Cause values in my report along with all the other non-blank data I actually want that would not be there at all with an inner join. Otherwise inner join is generally the way to go.
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 27 May 2015 at 8:14am |
|
Thanks, Erik, I'll give it a try.
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 28 May 2015 at 4:16am |
|
Good morning, Erik. I checked all my links and they are already inner joins.
I'm wondering if the issue is my record selection formula. I need the cases where *either* (TableA.Field1="x" and TableA.Field2 is in April) *or* (TableB.Field1="y" and TableB.Field2 is in April)
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
Erik
Groupie
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
|
Posted: 28 May 2015 at 8:57am |
|
It may be possible you are getting duplicate data because you are querying two different tables in your select criteria, but that's just a guess.
Are TableA and TableB linked together already? If so, what data is linking them together? If they are linked on a common value then maybe you can consolidate the April date criteria.
It's a bit difficult to conceptualize without a data mapping.
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 29 May 2015 at 2:34am |
|
Good point! They are not linked to each other but by a common table (which in turn is linked to another table or two). Okay, I'll keep playing with it. Thanks!
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
|
|
