Help find DataStage function !!

its_me48 · Post by **its_me48** » Thu Jul 07, 2005 1:51 pm

Hello All,

I want to find the word "Reversal" from one of the column in the transformer stage from the string.
Example Column A has 100 rows, first 5 are
Client Reversal,
REVERSAL Coding W/O
Contractual Allowanced
Diversified Payment Reversal &
Patient Payment

I want to add a logic in column B which is amount field (decimal datatype).
The logic is if I find the word "Reversal" or "reversal" or "REVERSAL" in column A, I want to change Column B to negative (-) amount else leave it as positive.
Is there a function that would help me find specific word in the string?

Please help.

Thank You,
-Sam

crouse · Post by **crouse** » Thu Jul 07, 2005 2:00 pm

Sam,

Either the INDEX or COUNT functions should work for you.

The help screens give examples.

-Craig

its_me48 · Post by **its_me48** » Thu Jul 07, 2005 2:13 pm

Craig,

Thanks for the reply. The index or count functions are different from what I am trying to do. I want to find the word "Reversal" (case insensitive) in the string? Any idea how I would make that search case insensitive?

Thanks!!

pnchowdary · Post by **pnchowdary** » Thu Jul 07, 2005 2:13 pm

You can use the following logic in your transformer to get the desired result

If (Count(UPCASE(INPUTLINK.COLA),"REVERSAL")) > 0 Then (INPUTLINK.COLB = INPUTLINK.COLB * -1) Else INPUTLINK.COLB

Try it and let me know if it works for ya.

its_me48 · Post by **its_me48** » Thu Jul 07, 2005 2:18 pm

Thanks Chowdary. I will give that a try and let the forum know probably tomorrow.

Regards !!

pnchowdary · Post by **pnchowdary** » Thu Jul 07, 2005 2:32 pm

There was a small error in the logic i mentioned above, Please use the below logic in your transformer

Code: Select all

If (Count(UPCASE(INPUTLINK.COLA),"REVERSAL")) > 0 Then (INPUTLINK.COLB * -1) Else INPUTLINK.COLB

its_me48 · Post by **its_me48** » Thu Jul 07, 2005 6:06 pm

Chowdary,

Thanks for the input.
Could you please check the below syntax. Sorry for asking lame questions but I am new to DataStage. I want to check for NULL values.
So, I want to add the below logic only if COLA is NOT NULL. How do I do it? Below logic is erroring for me.

If IsNull(INPUTLINK.COLA) = 0 AND If (Count(UPCASE(INPUTLINK.COLA),"REVERSAL")) > 0 Then (INPUTLINK.COLB * -1) Else INPUTLINK.COLB

Thanks!!

its_me48 · Post by **its_me48** » Thu Jul 07, 2005 6:37 pm

Looks like I might have got it...Please confirm...
Also could you please tell me if its going to find all reversals in the string; REVERSAL, Reversal, & reversal in the UPCASE(count) function below...

Thanks !!

If (IsNull(INPUTLINK.COLA) = 0 AND Count(UPCASE(INPUTLINK.COLA),"REVERSAL") > 0) Then (INPUTLINK.COLB * -1) Else INPUTLINK.COLB

chulett · Post by **chulett** » Thu Jul 07, 2005 11:18 pm

Me48, a couple of points:

You don't need to check the results of an IsNull() call against 0 or 1, even though that will work. Being a boolean, it is easier to read if you just say

If IsNull(INPUTLINK.COLA) to see if COLA is a null -or-

If Not(IsNull(INPUTLINK.COLA)) to see if COLA is not null

You don't need the not null check in your expression, it doesn't really add any value as a null string won't trigger any false positives in your Count check. You should be fine with pnchowdary's original version... and yes, because you upcase the field and check against 'REVERSAL' it will work not matter what the case in the original string.

pnchowdary · Post by **pnchowdary** » Fri Jul 08, 2005 10:13 am

Hi Me48,

I am sorry that I didnt check the website until now. I guess all your doubts have already been cleared by craig. If still you have any problems, let me know and I will be glad to help you out.

Thanks,
Naveen

its_me48 · Post by **its_me48** » Fri Jul 08, 2005 4:07 pm

Craig & Naveen, Thanks a lot. The solution worked like a charm.

-Sam