DSXchange

Hi,
I have data coming like
ORDER_TCD COST_CNTR_TCD NETWORK_TCD
S83010 11111
600009 33333
700006 22222 AAAAAA

After transformation i want it to look like

ORDER_TCD COST_CNTR_TCD NETWORK_TCD
S83010 11111
600009
700006 22222 AAAAAA

So basic idea is if ORDER_TCD starts with prefix S or network_tcd is not null don't do anything to cost_cntr_tcd else setnull

i am doing below transformation but not getting desired results any idea what is wrong



IF TRIM(to_transf4.ORDER_TCD)[1,1]="S" OR TRIM(IsNotNull(to_transf4.NETWORK_TCD) )
THEN to_transf4.COST_CNTR_TCD else SetNull()

What is the output you are getting?? What is the use of trim after is not null??

i am getting same output like input so no transformation is done

did you try without trim? trim() is not required after IsNotNull() check.

Hello times29:

I agree with priyadarshikunal that the trim does nothing. The IsNotNull will return a 0 if it is Null and 1 if it is not, so no need to trim. Are you doing this as a Stage Variable or a Derivation? I have seen "unexpected results" when using SetNull in a Stage Variable.

A quick test is to replace the SetNull() with some value, like "99999", to see if the issue is somehow related to the SetNull and how it is used.

Another potential solution is to use a stage variable, FlagCost=IF TRIM(to_transf4.ORDER_TCD)[1,1]="S" OR IsNotNull(to_transf4.NETWORK_TCD) THEN "Y" else "N".

For the to_transf4.COST_CNTR_TCD derivation, use If FlagCost="Y" Then to_transf4.COST_CNTR_TCD else SetNull(). Of course verify that the column is Nullable .

Yes you can do it all as a column derivation, but it will be easier to maintain and to troubleshoot as a Stage Variable. To test/troubleshoot, you can 1) add a column to the output link, 2) put the StageVariable into that column's derivation and 3) output to a peek stage to see the "Y" or "N" result of the IF Trim.... in that column. Once you get the proper result, "Y" or "N", you can then move the StageVariable derivation to the derivation of the COST_CNTR_TCD column replacing the "Y" and "N" appropriately.

However, the more I think about this, is the COST_CNTR_TCD column a numeric field? If so, does the derivation you describe have to explicity set a null value or can you use "", empty string? The replacement of SetNull with a "9999" as indicated in the quick test will shed some light to determine if this is "Null in a numeric field problem".

Thanks

God Bless!