Error logging

[senthil_tcs]

Hi,

My requirement is to reject and load the records which fail the business rule. Currently my job design is

seq stage->transformer 1->transformer 2->dataset1 and dataset2

Seq file to read the data from text, transformer 1 is to split the trailer and data record, transformer 2 is to validate the records for business logic. There can be multiple failures for a single record; we need to capture all the failures. Records which pass all the business validation moves to DataSet1 and rejected records will be moved to dataset2. Currently I am populating the different error codes against the same record with a delimiter.

The output of dataset 1 is

Emp_no Emp_Name Emp_Sal Status
1 A 100 Validation passed
2 B 100 Validation passed

The output of dataset 2 is

Emp_no Emp_Name Emp_Sal Status Error code
3 Validation Failed C2,C3
4 B Validation Failed C3

Business wants the output as below (dataset3)

Table Emp_No Error code
EMP 3 C2
EMP 3 C3
EMP 4 C3

Can someone help me how to create the output using transformer 2 and send the detail to a new dataset 3 in the above format? Option ops out of my mind is to use dataset2 and load the table errog log table as we have comma as the delimiters between different error codes (i feel this option is not efficient)[/img]

[Sainath.Srinivasan]

send the reject rows from tx2 through to a funnel stage and its output to ds2

[senthil_tcs]

send the reject rows from tx2 through to a funnel stage and its output to ds2

Thanks for your response. I am not clear how funnel will help us in this case. In the transformer for a single record we will have multiple error codes i.e. for emp_no 3 emp_name & emp_sal is null so using stage variables and populated c2 & c3. Similarly for emp no 4 only emp_sal is null so we have populated c3. In my output I have only three column table_name(can be populated by hardcoding the value), emp_no will be taken from source. But how we can map multiple stage variables(2 stage variables is this example 1 to check for emp_name and other to check emp_sal) to a single column error_codes in ds2.

[Sainath.Srinivasan]

You will have multiple output links from the tx, each representing one possible error.

There are other methods to do this. But this will be simplest to design and implement - in my opinion.

[senthil_tcs]

You will have multiple output links from the tx, each representing one possible error.

There are other methods to do this. But this will be simplest to design and implement - in my opinion.

Thanks, this should work but the problem the job design will look very complicated as we have around 100+ attributes to check and also the development time will be more. Do you have any other better approaches?

[chulett]

That just looks like a Pivot to me, why not perform the pivot of using the maximum number of output columns and then trim null results post pivot?

[Sainath.Srinivasan]

Write your rejects into a single string and pivot it into multiple records.

Craig did beat me into the post.

[senthil_tcs]

That just looks like a Pivot to me, why not perform the pivot of using the maximum number of output columns and then trim null results post pivot?

Thanks it worked. But the output from pivot is not coming as NULL, so filter is not working to remove the NULL value. I tried using trim function to check if there any blank spaces populated by pivot and put a constraint to remove records with NULL valus, still no luck. Please let me know if any format to be changed in pivot so it passes NULL Value for the columns for which there are no associated values.

[senthil_tcs]

That just looks like a Pivot to me, why not perform the pivot of using the maximum number of output columns and then trim null results post pivot?

Thanks it worked. But the output from pivot is not coming as NULL, so filter is not working to remove the NULL value. I tried using trim function to check if there any blank spaces populated by pivot and put a constraint to remove records with NULL valus, still no luck. Please let me know if any format to be changed in pivot so it passes NULL Value for the columns for which there are no associated values.

I have used trim funtion and set null function to set the blank values to NULL before sending it to pivot. This fixed the issue.

[chulett]

Good job on working that one out.