How to Apply the rule before cross domain Analysis

divine · Post by **divine** » Wed Jul 27, 2011 10:31 am

I have a file with 3 columns. My requirement is to check the file with a master file which has 7 columns .And I need to compare only 3 field of the first file.

Out of the 3 column in one of the column the value contains the currency code in a single character while the other file contains 3 digit codes.

e.x
In File A (with 3 column) the code is C which is CAD as per out Business Rule.

In File B (With 7 column) the code contains as CAD and other currency code like USD.

So I need to convert the file A data from C to CAD before comparison.

File A contain 5000 Records while File B contain 60000 records.

Could you please let me know how I need to do it?

I am a beginner to Information Analyser.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 27, 2011 4:54 pm

You need to pre-process the data, perhaps with DataStage, to generate compatible (comparable) values.

vmcburney · Post by **vmcburney** » Wed Jul 27, 2011 9:28 pm

Information Analyzer does not convert any data, it is a profiling and monitoring tool only. The only way to convert data via Information Server is via DataStage or QualityStage. Information Analyzer can profile the data and tell you what values are in the file and it lets you add data monitoring rules so you can monitor how accurate the codes are. DataStage or QualityStage will read in the rows from File A, perform a lookup against File B and output data merged from both files to File C or a database table. It can write exceptions (invalid lookup codes) out to a rejection file.

harshada · Post by **harshada** » Thu Jul 28, 2011 7:33 am

Not to barge in this thread, but as my query is exactly similar want to know this. Is is possible in Information Analyzer to apply rule to data, for eg : If value = 'C' convert it to 'CAD' and then run the 5 analysis? Or the rules are only used to view specific kind of data as we want to have it.

vmcburney · Post by **vmcburney** » Thu Jul 28, 2011 11:06 am

You can apply a rule in Information Analyzer using the reference data tables. You can for example use the in_reference_column operator to see if C is in a reference table containing the currency mappings of C to CAD. You cannot apply this rule to switch C to CAD for further analysis.

The better product for profiling of data transformations such as this is InfoSphere Discovery where you can discover the rule that C should become CAD and the other mappings of this table and then view data profiling statistics for the derived field and derived target table before you write any ETL. It's called Transformation Discovery and it's a subset of the InfoSphere Discovery functions.

Like Information Analyzer the InfoSphere Discovery tool is not an ETL tool and cannot change data however it can internally discover transformation rules and apply these changes to the profiling metadata to show what the resulting data would look like.

harshada · Post by **harshada** » Thu Jul 28, 2011 11:22 am

Thanks Vmcburney. Got your point. Over to Divine now

divine · Post by **divine** » Fri Jul 29, 2011 9:46 am

ray.wurlod wrote:You need to pre-process the data, perhaps with DataStage, to generate compatible (comparable) values.

Thanks for the valuable information. I'll take care of this.