DSXchange

i have small req i am doing a look up to a fileset to get col B on key col A , A
is a key field in driver tbl and lookup fileset, if the lookup fails then col B will be empty
then i need to see if col A = 'REQ' then col B shud be "Request" else "Deny".
my code :
If IsNull(col B) or trim(col B) = ' ' Then If col A = 'REQ' then col B= 'Request' else col B= 'Deny' else col B
which comes in transformer next to lookup stage , but in the out put iam getting nothing its empty.

Thanks in Advance

Try putting Parentheses in your code.

If IsNull(col B) or trim(col A) = ' ' Then (If col A = 'REQ' then col B= 'Request' else col B= 'Deny') else col B


I am not sure if this is the problem. But you can try it.

I even tried that but still it is not working.

If IsNull(col B) or trim(col A) = ' ' Then If col A = 'REQ' then col B= 'Request' else col B= 'Deny' else col B

Say you have two links one is the lookup link 'lu' and the other is the input link ip.

Try this code in the derivation of ColB in the output

Actually i am doing this in transformer so there is only input link not lookup,

sorry for the mistake.

Minhajuddin wrote:If IsNull(col B) or trim(col A) = ' ' Then If col A = 'REQ' then col B= 'Request' else col B= 'Deny' else col B

Code: Select all

if(IsNull(ip.ColB)) then ( if(trim(ip.ColA)='REQ') then 'Request' else 'Deny') else lu.ColB

In the code above just change the "lu" to "ip" and then try.

Try this ---
|
|
V

Minaj --I used your code but still iam getting only spaces, i checked the output i am getting from lookup stage, it returns 50 spaces since col B is char(50). i tried to remove spaces and put it as varchar but i am not getting it.

samsuf2002 wrote:Minaj --I used your code but still iam getting only spaces, i checked the output i am getting from lookup stage, it returns 50 spaces since col B is char(50). i tried to remove spaces and put it as varchar but i am not getting it.

What didn't you get?

Can you give us some more info about your requirement?

I should get Request else Deny in col B depending on value of Col A when the lookup fails. when the lookup fails Col B should be empty or null then i should check what is the value in Col A (which is a lookup key ) if it is REQ then i should get Request else Deny in col B.

One last try

Here you go


Say you have a link called 'ip' to the transformer.

Use this code in the derivation of ColB.
This thing should work.
If it doesn't please put the exact code you are using in the transformer.

samsuf2002, one think I see is that, when you are doing trim(col B), you should compare it to '' not ' '. Are you doing that? It seems like you are not.

Trim() can never generate "".

Ray is right !! .

My output from lookup stage which doesnt matches the key is coming as 50 spaces i need to reduce it to one space,empty or null. How can i do that ?

Hi Sam,
Did you try reading the output as 'varchar'.I mean,did you try defining the column as 'varchar' in the output metadat of lookup itself?

Trim() will get you to a single space. Convert() can get to "".