Currently, I am using the Count function inside a Transformer stage in a parallel job to check if the values of 1 Stage Variable (StageVar) contain some explicit value then give another column (Code) some values.
There's so many Code to check in the StageVar but I'll just take the 'D' code for example:
If the StageVar contains 'DEBT' (and only 'DEBT', not any other string like 'DEBTOR' or 'ODEBT', etc) in its value, the Code column for that row will have the 'D' code.
Here's the code that I use in the Code column's derivation:
If Count(StageVar, 'DEBT') > 0 Then 'D' Else SetNull()
It succeeded in giving the code 'D' to which row that the StageVar contains the 'DEBT' string. But the problem is the content of the StageVar is not always having the 'DEBT' string that stands alone.
There're some cases that the StageVar may contain the 'DEBTOR' like 'PAY DEBTOR STATEMENT IN NOVEMBER' or 'ODEBT' like 'PAYMENT ODEBT FOR NOVEMBER', which I'll ignore and not giving the 'D' code for the Code column.
But the above code also giving the 'D' code for them.
Do you guys have any idea how to fix this inside the Transformer stage itself?
Thanks!
Make sure that StageVar has a leading space and a trailing space. For example " NATIONAL DEBT IS OUT OF CONTROL "
You could use Count() function, but Index() is probably more what you want. Also encase your search string in space characters.
If Count(StageVar, " DEBT ") > 0 Then "D" Else SetNull()
If Index(StageVar, " DEBT ", 1) > 0 Then "D" Else SetNull()
Index() stops searching as soon as it finds the first occurrence. Count() must perforce search to the end of StageVar.