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
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
-Dell
DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com
RE: Complex question concerning Crystal Reporting
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 how does that help? You can't determine which of the last 2 are USA!
RE: Complex question concerning Crystal Reporting
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
Is Honey Kong in the USA?
RE: Complex question concerning Crystal Reporting
RE: Complex question concerning Crystal Reporting
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
RE: Complex question concerning Crystal Reporting
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
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!