Lookup table

[ekta22]

We've been asked to add a new field to our tables so that the users can run the reports based on that new field.
This new field is based on 3 existing fields in the table.
I created a crosswalk table that has all the mappings between the existing fields and the new field.

Here is some sample data for the crosswalk-

- If old field1 is "AL", field2 is "2E", field3 is "H" then the value in the new field should be "WWY1V"

- field1 = "AL"
field2 = "2H"
field3 = "H"
New Field = "WWY1D"

- field1 = "AL"
field2 = "2H"
field3 = "K"
New Field = "WWY1D"

- field1 = "EA"
field2 = "1F"
field3 = "J"
New Field = "WEW1C"

I have already created the crosswalk with all these values.
My exisitng tables has millions of records, so instead of adding this new field in the table itself and populating it, I would like to use the crosswalk table as a lookup.
So if a user wants to run the report by the new field.. let's say WEW1C it will look at the corresponding value EA1FJ in the crosswalk and go to the table and pull up all the records with value EA1FJ. I hope it makes sense.

What would be the beest way to do this? Can I add this crosswalk table as it is to my exisitng report and then do the lookup. If yes, then how? I am not sure if I should join the crosswalk table with the exisiting views my report is based on. Totally confused. Any help is really appreciated.

Thanks

-E

 

[synapsevampire]

People generally start with posting their software version, then their database, then example data, then the expected output, then a description if they feel it required.

I think that you needadd the table, joinit by th lookupfields to your current table(s), then display the "new field" in the parameter.

How this is done is basedonyour software, please get through the technical aspects of a post first.

-k

 

[ekta22]

Sorry, I should have mentioned that.

I am using crystal 10 and SQL Server 2000 database.

CrossWalk table

Here is some sample data for the crosswalk-

- If Region is "AL", Sector is "2E", Subsector is "H" then the value in the new field should be "WWY1V"

- Region Sector Subsector New Field
---AL-----2H-----H---------WWY1D
---EA-----1F-----J---------WEW1C
---EA-----1B-----B---------WEW2E

Existing Table
- Region Sector Subsector Type
---AL-----2H-----H---------AAA
---AL-----2H-----H---------BBB
---AL-----2H-----H---------CCC
---EA-----1F-----J---------AAA

User enters 'WWY1D' in the parameter field

Result should show the first 3 records from the Exisiting table.

Results -
- Region Sector Subsector Type
---AL-----2H-----H---------AAA
---AL-----2H-----H---------BBB
---AL-----2H-----H---------CCC


My reports are baed on views. I created a new view in which I created an inner join between the exisiting view and the crosswalk table using the Region, Sector and subsector field as that's the common field but it did not return the right result.

How should the join be done?

Thanks

-E

 

[ekta22]

anyone?

 

[IdoMillet]

Why can't you simply do a 3-column join to the new table?

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ekta22]

This is what I did. I joined my view with the new crosswalk table and joined it on the 3 fields but its returning a lot more data.

SELECT view.*, crosswalk.sector_code, crosswalk.New_Field
FROM crosswalk crosswalk INNER JOIN
view view ON crosswalk.sector_code = view.REGION + view.SECTOR + view.SUBSECTOR

 

[lbass]

You should be linking the new table to the existing table on each of the fields separately: region, secton, and subsector. Then you should be able simply display {newtable.newfield} on your report.

-LB