Complex question concerning Crystal Reporting 2

[badword]

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

 

[hilfy]

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

http://www.decisionfirst.com

 

[badword]

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)?

 

[SkipVought]

So here's your result with an left outer join

[pre]
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
[/pre]

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

 

[badword]

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?

 

[SkipVought]

The excel file contains only USA records"

Is Honey Kong in the USA?

 

[badword]

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.

 

[badword]

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.

 

[SkipVought]

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

[badword]

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...

 

[badword]

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!