dropping null columns in tranformer

[sravanthi]

Hi

How can we drop null column in a transformer.

i have 250 columns in the job.i have to drop all the column which are null
how can we achieve this

Thanks!

[opdas]

In a transformer a record can be dropped based on condition but not a whole column.It doesnt sound logical can you explain it more briefly.

[kumar_s]

You cannot change the metadata dynamically, but you can achive you requirement by some work arround.
Read all the 250 column as single varchar column along with delimeter. Check for '' between two delimeter. If so exclude it.
Say if you delimeter is ',' then something like if inputfield[i,2]=',,' then '' else <value>, you need to loop for all the character in the field.
You can use any custome subroutine, or with 250 stage variable.

[vmcburney]

Sounds like a good candidate for a custom built stage. Something that could navigate through the list of columns looking for nulls in a loop with an output link and a reject link.

It is code intensive but you can do it in a transformer stage. Most functions will reject a row if that row has nulls in it, therefore you do not necessarily need to check for nulls, you just need to run a function on any field and have a reject link.

For example you can run a trim on every string field, you can check for zeros or negative numbers or high values on numeric fields, you can validate date fields etc. Any time a null is encountered the row will be rejected because parallel functions do not like nulls.

[kumar_s]

Another similar thought, if you read as single varchar string with delimeter in it, you can use the string functions to replace, such as convert(',,','',Input.field), if it is a comma delimited.