Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Comparing 2 Excel Sheets with Crystal Report 9 Post Reply Post New Topic
Author Message
borg_jos
Newbie
Newbie


Joined: 24 May 2010
Online Status: Offline
Posts: 3
Quote borg_jos Replybullet Topic: Comparing 2 Excel Sheets with Crystal Report 9
     Posted: 29 Jul 2012 at 12:08am
I have two Excel Sheets and need to report all results which do not match between the two sheets.
 
Example:
Sheet1:
Field1             Field2
123 15
456 20
788 30
999 20
 
Sheet2:
Field1             Field2
123 10
456 20
789 30
999 20
 
I need to report the following:
Sheet 1                                   Sheet2
Field 1          Field 2                 Field 1       Field 2
123                   15                  123                10
788                   30                  789                30
 
I have tried to link all fields (Inner Join / Link Type =) however I get only the results which do match wheareas I want the exact opposite. I have tried to Outer Join but it still does not work.
 
If I try Link Type != then Crystal will report all records :(
 
Any ideas on how to obtain the desired results?
 
 
 
IP IP Logged
borg_jos
Newbie
Newbie


Joined: 24 May 2010
Online Status: Offline
Posts: 3
Quote borg_jos Replybullet Posted: 29 Jul 2012 at 5:45am
What I really think I need is a Full Outer Join ..... however this option, for some reason is greyed out.
 
 
Anyone knows what the reason might be?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 29 Jul 2012 at 7:51am
Because these are Excel files and not a "real" database, you don't get the full outer option.  You might be able to simulat this, though.
 
1.  Don't join the tables - Crystal will tell you that this configuration is not generally supported, but that's ok because we're going to do this with the Select Expert.
 
2.  Go to the select expert for there report.  Assuming there is one key column that the two sheets are joined on, edit the selection formula and enter something like this:
 
(IsNull{Sheet1.KeyColumn} or IsNull({Sheet2.KeyColumn}) or {Sheet1.KeyColumn} = {Sheet2.KeyColumn})
 
This should simulate a full outer join for you.
 
-Dell
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum