Reject Empty Rows

Emilio · Post by **Emilio** » Fri Oct 14, 2005 2:22 pm

Hello,

I have a sequential file that is showing empty but is really not empty, the file shows having empty rows like this:
,,,,,,,,,,
,,,,,,,,,,
for one or more rows.
How can I reject a row if is empty?

Thanks,
Emilio

kcbland · Post by **kcbland** » Fri Oct 14, 2005 2:51 pm

The first help is to point out you are posting in the wrong forum. (editor's note: Topic has now been relocated)

The second help is to suggest putting a constraint in place in a transformer to throw away any rows that have a blank value where one isn't allowed. You can't really avoid reading those rows, the best thing to do is send them to an exception file or just throw away.

Emilio · Post by **Emilio** » Fri Oct 14, 2005 2:59 pm

Thank You,
Should I check each field for null or is it possible to check if the entire row is empty?

ray.wurlod · Post by **ray.wurlod** » Fri Oct 14, 2005 6:53 pm

You need to check every column. The Format tab of your Sequential File stage specifies that "" is to be understood as NULL, so you need either to change this or to check using the IsNull() function.

Another possibility is to read the file as if it contains just one large string column, and then to use a constraint such as

Code: Select all

inLink.TheRecord <> ",,,,,,,,"

In the same Transformer stage you can then parse "good" rows into appropriate columns.

pavanns · Post by **pavanns** » Sun Oct 16, 2005 12:43 pm

how abt using reject rows tab ON in transformer

ray.wurlod · Post by **ray.wurlod** » Sun Oct 16, 2005 4:27 pm

I hate that. It generates a warning in the job log. My idea of best practice is that no job that executes successfully generates a warning.
Prefer a second output link whose constraint expression is the logical inverse of the constraint expression on the "good rows" output link.

srinagesh · Post by **srinagesh** » Wed Oct 19, 2005 11:32 am

How about stripping the file of the blank records using unix command .

This will solve the problem I guess..

what say ?

Emilio · Post by **Emilio** » Wed Oct 19, 2005 11:55 am

I am in the windows enviroment but I was able to do it using the following contraint. Perhaps someone have a better way to do it:
trim(NullToEmpty(xfmMissCode1.ArmConvOpt)) <> "" OR trim(NullToEmpty(xfmMissCode1.ArmIndexCode)) <> "" OR trim(NullToEmpty(xfmMissCode1.ArmNegAmrFlag)) <> "" OR trim(NullToEmpty(xfmMissCode1.ArmRoundFlag)) <> "" OR trim(NullToEmpty(xfmMissCode1.BorrSelfEmpFlag)) <> "" OR trim(NullToEmpty(xfmMissCode1.BorrSex)) <> "" OR trim(NullToEmpty(xfmMissCode1.CoBorrSex)) <> "" OR trim(NullToEmpty(xfmMissCode1.DocumentCode)) <> "" OR trim(NullToEmpty(xfmMissCode1.DwellTypeCode)) <> "" OR trim(NullToEmpty(xfmMissCode1.LimitDocFlag)) <> ""
er

ray.wurlod · Post by **ray.wurlod** » Wed Oct 19, 2005 5:35 pm

Nothing wrong with that. Whatever you do, if you've read multiple columns you have to test them all.

The only other approach is to read the file as if it contains just one column, and only allow it to pass if it contains more than just commas (you could test its length, for example Len(InLink.TheSingleColumn) > 9). Then use output column derivation expressions using the Field() function to parse that single column into the requisite number of discrete columns.