INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Complex question concerning Crystal Reporting

Complex question concerning Crystal Reporting

(OP)
I am working on a DB2 database, and I need to compare data from an excel worksheet. The excel worksheet contains the account number along with other information. I am able to join the account number from the excel worksheet with the account field from one of the tables in the DB2 database.

I would like to list all the records from the excel that is not found in the DB2 database that meets a specific criteria.

Example:
Excel file contains the following fields:
Acct#, Cust_Name, Address, City
12242, Jon James, 123 St., New City
A2524, Mike Von , A52 AVE, New York
B5245, Sue Tau , 552 DRV, Honey Kong

DB2 table,
Acct#, Country, PriceID, RepID
12242, USA , 1546622, James
54245, USA , 5456573, James
25645, JAPAN , 5456237, Wong
A2524, Georgia, 5597348, Michelle
55645, Jamaica, 4678735, Trevor

as you can see both account# 12242, and A2524 are in the DB2 table and the excel file; however, I only want to report to compare against data in USA. Therefore, from the 3 accounts in the excel file, the report should only list A2524, and B5245

I only have the option to select the Left Outer Join and the Inner Join

RE: Complex question concerning Crystal Reporting

It appears that you want to show all of the non-USA records from the Excel file, regardless of whether they have a corresponding record in the DB2 table. If that's correct, I'm not sure you're going to be able to do this with the data you have. The problem is that you don't have a country field in the Excel file, so there is no way to filter that to show just non-USA records unless the records have a corresponding row in the DB2 table where the country is located.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Complex question concerning Crystal Reporting

(OP)
Ummm... while I am having trouble doing it... Do you not believe that if I use the Add command (That is the Add Command in the Database Expert) to create a new table with the filtered information. Example: Say I create an SQL filter in the Add command to list only records in the USA in the DB2 table. My DB2 Table should now look like the following:

DB2 table,
Acct#, Country, PriceID, RepID
12242, USA , 1546622, James
54245, USA , 5456573, James
Example:

Excel file contains the following fields:
Acct#, Cust_Name, Address, City
12242, Jon James, 123 St., New City
A2524, Mike Von , A52 AVE, New York
B5245, Sue Tau , 552 DRV, Honey Kong

Then I can do a Left Outer Join (adding the Excel table to the left); then under Link Options set link type to != (not equal)?

RE: Complex question concerning Crystal Reporting

So here's your result with an left outer join


Acct#	 Cntry	 PrceID	 RepID	Acct#	 Cust_Name	 Address	 City
12242	 USA 	1546622	 James	12242	 Jon James	 123 St.	 New City
54245	 USA 	5456573	 James				
				A2524	 Mike Von 	 A52 AVE	 New York
				B5245	 Sue Tau 	 552 DRV	 Honey Kong
 

So how does that help? You can't determine which of the last 2 are USA!

RE: Complex question concerning Crystal Reporting

(OP)
SkipVought...

Here is how: The excel file contains only USA records, the DB2 database contains both USA and non-USA records.

So, if I am able to use Add Command to create a new table with only USA records. Now I'm working with two tables that contains only USA records. by linking the account number field together in both table and using the =! (not equal) join type, this should give me a list of records that are not in both table, no?

RE: Complex question concerning Crystal Reporting

"The excel file contains only USA records"

Is Honey Kong in the USA?

RE: Complex question concerning Crystal Reporting

(OP)
:) I see your point... but with respect to this exercise, yes... if it is in the excel file, then It is a USA record... basically what I need to do is find out which account number in my excel file that is not in my DB2 database. the relationship between the excel file and the DB2 is 1 to many. Which means, the account number is unique in the excel file, but may exist multiple times in the DB2 database. Therefore, it is possible to have the same account number for USA record and a non-USA record. As such, I do not want to include the non-USA record.

RE: Complex question concerning Crystal Reporting

(OP)
SkipVought,

I appreciate your help with this, but i was reviewing your results... and I don't believe a left out join will produce the result you mentioned. The left out join will only produced records found in the left table, assuming an enforced is not triggered.

RE: Complex question concerning Crystal Reporting

(OP)
I agree with the information from the link you send. that is why i said the left outer join will not show the following records:

Acct# Cntry PrceID RepID Acct# Cust_Name Address City
12242 USA 1546622 James 12242 Jon James 123 St. New City
54245 USA 5456573 James
A2524 Mike Von A52 AVE New York
B5245 Sue Tau 552 DRV Honey Kong

a left out join will show the record as:

Acct# Cntry PrceID RepID Acct# Cust_Name Address City
12242 USA 1546622 James 12242 Jon James 123 St. New City
A2524 Mike Von A52 AVE New York
B5245 Sue Tau 552 DRV Honey Kong

As you can see the 54245 will not be apart of the left outer join be that it is only listed in the DB2 (right) table.

Thanks for assist with this... really appreciate the effort...

RE: Complex question concerning Crystal Reporting

(OP)
So my solutions:

while I maybe using too many commands... for now it got me the results:

I was able to use the Add Command to create a new table.

The add command was created under the DB2 database:
SELECT Field1, Field2
FROM Database.Table
WHERE Country IN (USA) // I could have used =, but I wanted to make it easy if I had to include another country.
and region in (02) // this was another filter i had to use to correctly identify my data..

I label the command ModTbl
note: Field1 contains my account# and field1 contacts my region

Now I have a table with data that i want to use to compare with my excel file.

Next I left outer join (excel file in left) with field1 (from the ModTbl table)

Enforce Join is set to "Not Enforced"
Link Type is set to "="

Then I create an if statement formula under the Formula Fields

if isnull ({ModTbl.Field1}) then 1 else 0

then I set the Select Expert:
{ModTbl.Field1} = 1

That is what I had to do to get the result i want... the only problem I have is, the report takes a long time to run... therefore, I have to automate it... and set it to run before I get into the office.

Thanks all!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close