Comparing CFF files , Record to Record !

ady · Post by **ady** » Fri Dec 15, 2006 1:59 pm

I have two CFF files one from the Production and one from the DEV. Now I need to compare "records" / "fields" for these two files to check if the data from both the ENV matches.

Some fields in DEV data are not in Production yet, in that case we'll have to tell the job not to perform a compare record-for-record in those cases, but rather field-to-field. I need to compare all rows !!

I think I need to use indicators for each field which say "Y" if the data matches, "F" in the field matches and "N" if there is a mismatch. Indicators are not that important but I need a way to compare the data (i think indicators are the easiest to do it).

Any Ideas ??

ray.wurlod · Post by **ray.wurlod** » Fri Dec 15, 2006 3:42 pm

Do you have Enterprise Edition? There are better tools in parallel jobs. In a server job, you're up for a lot of stage variables and hand-coding.

ady · Post by **ady** » Fri Dec 15, 2006 3:55 pm

I am required to do it on Server. Can I do it? and How can I do this on parallel ?

ray.wurlod · Post by **ray.wurlod** » Fri Dec 15, 2006 4:12 pm

Of course you can do in in server. Set up a stage variable for each comparison. Tedious, perhaps, but not difficult.

Parallel jobs have inbuild stage types for change detection (Difference, Compare and Change Capture stages). They tend to make this kind of task less tedious.

narasimha · Post by **narasimha** » Fri Dec 15, 2006 5:31 pm

Check out Merge Stage, you can extend its usage to see the differences in the the two files.
You can do things like Left Outer Join, Right Outer Join,....

ray.wurlod · Post by **ray.wurlod** » Sat Dec 16, 2006 4:36 pm

Merge stage will only show differences on columns defined as keys for the join.

narasimha · Post by **narasimha** » Sat Dec 16, 2006 6:13 pm

ray.wurlod wrote:Merge stage will only show differences on columns defined as keys for the join.

Thats right, missed that part.

ady · Post by **ady** » Sun Dec 17, 2006 1:04 pm

How can I setup a stage variable ? .... Please explain ?

I need to compare each field in the record !

ray.wurlod · Post by **ray.wurlod** » Sun Dec 17, 2006 2:30 pm

In the Transformer stage you open the stage properties window (use the leftmost tool on the toolbar), then select the Variables tab.
You will need one stage variable for each field. The result of the expression is whatever you like, either a true/false, or a "flag" as suggested earlier.

You may also need to "show stage variables" so that you can see the stage variables grid in the design canvas.

Further stage variables can be used to consolidate the overall answer (for example, record contains no changes).

kumar_s · Post by **kumar_s** » Sun Dec 17, 2006 5:35 pm

If its a delimited file, first count number of delimiter using either DCOUNT or other functions available. If it the number of fields is as expected, do a lookup against production file. If all the fields matches, marks the flag as 'Y'.

ady · Post by **ady** » Mon Dec 18, 2006 8:23 am

Specs just changed ... i have to compare sequential files now, and on parallel jobs !

Which is the best option i have right now Difference,Change capture or compare ?

ray.wurlod · Post by **ray.wurlod** » Mon Dec 18, 2006 2:45 pm

Depends what you want out of it. Why not start a new thread on the parallel forum?