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()
if then else logic not working
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 19
- Joined: Tue Feb 23, 2010 11:15 am
- Location: Los Angeles
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!
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
![Wink :wink:](./images/smilies/icon_wink.gif)
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!
Darryl