Logic for implementing

[vskr72]

I need to capture certain data in error table which has the following feilds:

- SrcFileName
- ColumnName
- ColumnValue

The basic rules for identifying a bad value is like - checking if the column is not a valid number or if its not a valid date. For eg., the Src looks like this:

Code: Select all

EMP_ID        B_DT              Age        Salary      Join_DT
1000          1972-01-01        39           500B       19AP-01-01

In the above example Salary and Join_DT are bad values. So, they need to be captured in the error table like below:

Code: Select all

EMP_ID       SrcFileName        ColumnName              Columnvalue
1000          ABC.txt              Salary                     500B       
1000          ABC.txt              Join_DT                 19AP-01-01

Capturing columnvalue is not an issue. How do we capture columnnames? Any other way than hardcoding it?

[jwiles]

How do you plan on performing the actual data validation?

[vskr72]

I was thinking more on the lines of using 'IsValidDate' and 'IsValid' for numbers. I think that should work right.

[jwiles]

Ok, as you're going to perform validation within a transformer, create one or more output links specifically for the error table records and build those records based on the validations that fail.

[vskr72]

Thank you. But, how do we get the column name which failed validation.

[zulfi123786]

EMP_ID B_DT Age Salary Join_DT
1000 1972-01-01 39 500B 19AP-01-01


For the above example you could use 2 stage variables :

stgvar1 : This would be like

If not(isvalid("int32",EMP_ID)) then 1
Else if not(isvalid("DATE",B_DT)) then 2
Else If not(isvalid("int32",AGE)) then 3
Else If not(isvalid("int64",SALARY)) then 4
Else If not(isvalid("DATE",JOIN_DT)) then 5
Else 0


stgvar2 : I would be

If stgvar1=1 then 'EMP_ID'
Else If stgvar1=2 then 'EMP_ID'
Else If stgvar1=3 then 'AGE'
Else If stgvar1=4 then 'SALARY'
Else If stgvar1=5 then 'JOIN_DT'
Else ''

you can have the link contraint to feed your error table using

"stgvar1>0"

[chulett]

So, in other words...

Any other way than hardcoding it?

No.

[jwiles]

You should end up with multiple stage variables, with at least one for each column which acts as a True/False indicator for whether or not a column failed validation. Because you wrote the code, you should know which columns are failing and can hardcode the name.

Regards,

[fmou]

how do we get the column name which failed validation.

- capture to sequential file.
- in the property of the sequential file, tick the output field name option.

posting off the top of my head, actual wording might be different.