Loop logic in 7.0

[nkln@you]

I have a set of records coming from the source. For each record I should 6 columns whether any column is NULL. One record should be passed to output if one column is NULL. Suppose if we have all 6 columns NULLfor the same incoming record,then I should pass 6 records( same as source record) to target.

What is the best way to do this.

I thought of having 6 o/p links from transformer and in each constraint, I will checking for one column. So I will be having target stages.

But I think this approach is not correct. Is there any way to this.

[rameshrr3]

Convert the NULLS in a record to some character string using your NullToString() function.

Code should be somewhat like this

Code: Select all

If IsNull(InputLink.InputColumn) Then "_NULL" else InputLink.InputColumn

Pass all records where a single or more column has the value with _NULL to a second Transformer stage , using constraints
In the second transformer pass through all input columns to output column

The Check each record for the occurence of "_NULL", if it exists , output the column from a transformer stage to a PIVOT Stage, where you can give appropriate derivation in the output. Finally pass the output of pivot stage to a transformer where you will catch only the single column records where the value is "_NULL".

Im not sure if what i have written is clear to you.

But i can illustrate what above logic can do.

1, 2 records with 2 fields each as NULL --> output will have 4 records

2, 3 records, first has 3 fields NULL, second one has 2 fields null, 3rd one has 2 fields NULL--> output will have 7 records

In case you have not used the pivot stage,
I guess you will have to refine your understanding of what a PIVOT stage does, look up to it in the plug-ins guide

Finally , if anyone has a better way of expressing this, I welcome your thoughts.


Thanks
Ramesh

[balajisr]

Another approach is have 6 links coming out of transformer. Funnel these links into a single link and sent it to a single output stage.

[DSguru2B]

If you source is a flat file, ISNULL() might not work. Check for the Len() after trimming the field. If its 0, assume its null.