Custom code to validate multiple fields

Question

Hello,

I have transactional data with multiple dimension fields that should be validated again ten different lookup files. Each validation of dimension field will have different error code. There are two different output files. First output file has all good records which passed all the validations against the dimensional data and second output file has rejected records with all error code for particular record. Can you let me know how to achieve this functionality with minimum number of components.

Thank you

prady_9999 · Answer

Sample Data with comma delimited

First_Name,Last_Name,Employee_Code,Department,Sub_Department,Class

Alex,Foo,123,Sales,Auto,01

Prady,Sr,234,Finance,HR,07

1) First_Name and Last_Name fields should be validated against Employee table. If not present error_code is 'E01'. If present error_code field will be blank.

2) Employee_Code field should be validated against Employee table . If not error_code is 'E02' which is appended to error_code. For example 'E01|E02' which means record failed both validations

3) Department field should be validated against Department table. If not error_code is 'E03'

4) Sub_Department field against sub department table. Error code is 'E04'

Output file 1 will have only good records with error_code is blank

Output file 2 will have original record plus additional error_code field

I am a Ab Initio ETL tool developer. To achieve this functionality i use lookup files as dimensional data is not huge. I don't use any joins.

1) Unload all dimension tables and create separate lookup files

2) Read transaction data thru Input file component

3) Write all validation logic in reformat component. I use Data Manipulation Language to achieve this. There are lookup functions. I will call separate lookup functions to check if data is present in each lookup table. If not assign error_code

4) Then write records to separate flows one has good and other flow with error records.

I only have 4 components other than unloading lookup tables to files.

Input File -> Reformat -> Filter -> Output Files

mceleavey · Answer

@apathetichell , it could be a multi-field formula, it could be a transpose and a formula...it's a mystery.

apathetichell · Answer

the answer is probably using a macro - but how to implement it and where is very data and workflow dependent...