reject a record!!

[kennyapril]

A job is designed to load data into a table.

These records are unique records so before loading into the table remove_duplicates stage is used to remove the duplicates.

Finally all the records without the duplicates are loaded,one record among them has all fields null except a field which is "0" as it is an integer

Is there any function in transformer to remove that particular record before loading into the table?


Thanks

[chulett]

Transformer constraint would be one way.

[Ravi.K]

You need to write a stage variable to check whether all the columns are null or not except that integer column then write a constraint to load non nullable records into target.

This is helpful if the columns are less. There could be other Optimized work arounds...

[kennyapril]

Transformer constraint

There are 5 fields( 4 char,1 integer)

the 4 fields has null in that records and the other interger field has "0".

IsNotNull(dslink.Person_name)

where Person_name is one of the 4 fields.

can I use the above function in the transformer constraint to reject the single record?

[ray.wurlod]

This is an incomplete solution unless you can guarantee that Person_name is not null in every other row. Otherwise you should check all five fields.

[abhijitg]

As mentioned above, create a single stage variable (svRejectFlag) and evaluate it based on all the columns that would make it true or false, and apply the constraint on the stage variable.

Code: Select all

svRejectFlag = IsNull(in.Col1) Or IsNull(in.Col2) ..  

[kennyapril]

I removed all the duplicates by using remove _duplicates stage so the field person_name has only one row which has null and that is the key which I used in remove duplicates.

For the safe side I will use the stage variable svRejectFlag.

In this stage variable IsNull(in.col1) or IsNull(in.col2)...cnotains only the rows with null values.

When you give a stage variable as svrejectflag does it reject the records which we described in the stage variable.

suggest please!!

Thanks,

[ray.wurlod]

When you give a stage variable as svrejectflag does it reject the records which we described in the stage variable.

No.

But it does give you a good basis on which to construct constraint expressions to handle the rows that are "good" and those which you wish to dispose of differently.

[chulett]

A constraint is an expression. If the expression evaluates to "true" for a row, it is passed down the link. If the expression evaluates to "false" it does not. Simple as that.

[kennyapril]

When I use a stage variable as suggested svreject=...........

use that stage variable as constraint
something like

svreject =true in the contraint of that output.

Is this right?

[chulett]

Just svreject could work. But for this to make sense, svreject as "false" [ aka Not(svreject) ] would be used in your target link, while svreject as "true" would be for a reject link.

[kennyapril]

That works!!


Thank you ALL!!