IF then else not working in transformer

[s_porkalai]

Hi All ,

Please find the below job design

Code: Select all

sqfile -> sort_stage -> remove_dup -> transformer -> sqfile

And inside transformer i am using the following expression

Code: Select all

If (trim(link1.col1,' ','A')='msg1') then 'jb1'

else if ((trim(link1.col1,' ','A')='msg2') or (trim(link1.col1,' ','A')='msg3')) then 'jb2'
else 'Unknown'

The issue we are facing is, when we ran the job for the 1st few iterations the conditional statement given above is working fine.
But after running the same job for several iteration (upto 10 runs), the conditional statement is not working and
we are getting the output as 'Unknown' for all the incoming values(even if the incoming values are 'msg1' or 'msg2').

the length of link1.col1 is varchar(16).

Can anyone please help me to get rid of this strange error.

[balajisr]

Is there any difference between the iteration which worked and which did'nt. E.g Job Parameters etc..

Is there any pattern you can infer as to first X iterations works and last Y iterations does not work?

[s_porkalai]

No all the iterations are working with the same data.
even when i try to run the job in sequencer using start loop and end loop
ie

Code: Select all

startloop -> job-> endloop
        |               |
        |---------------|

activity 'if condition' is working fine for first few iteration in loop but it is giving ' UNKNOWN' after several runs

[OddJob]

As a matterof good practice, you may want to consider putting your 'trim' in a Stage Variable e.g.
svTrimCol1 = trim(link1.col1,' ','A')

Field derivation is then:
If (svTrimCol1='msg1') then 'jb1'
else
if ((svTrimCol1='msg2') or (svTrimCol1='msg3')) then 'jb2'
else 'Unknown'

It's more effiecient to perform the derivation once in a stage variable.

To test what's really going on, send the rows down a new link from the transformer to a Peek stage. Pass the following rows:
link1.col1
svTrimCol1

You may also want to use squote instead:
squote(link1.col1)
squote(svTrimCol1)
This will help you identify any whitespace that's sometimes difficult to spot in the peek logs.

Check peek output to confirm you're getting what you expected.

Also, be careful of NULLs going to a stage variable, it won't work the way you're expecting!

[OddJob]

As a matterof good practice, you may want to consider putting your 'trim' in a Stage Variable e.g.
svTrimCol1 = trim(link1.col1,' ','A')

Field derivation is then:
If (svTrimCol1='msg1') then 'jb1'
else
if ((svTrimCol1='msg2') or (svTrimCol1='msg3')) then 'jb2'
else 'Unknown'

It's more effiecient to perform the derivation once in a stage variable.

To test what's really going on, send the rows down a new link from the transformer to a Peek stage. Pass the following rows:
link1.col1
svTrimCol1

You may also want to use squote instead:
squote(link1.col1)
squote(svTrimCol1)
This will help you identify any whitespace that's sometimes difficult to spot in the peek logs.

Check peek output to confirm you're getting what you expected.

Also, be careful of NULLs going to a stage variable, it won't work the way you're expecting!

[s_porkalai]

we have tried with all the options - trim ,peek, squote. but the result is same . and the data we view through peek state is correct only.


one intersting fact is when we use alpha function on link1.col1 it is giving result as 0, eventhough there is no non alphabet characters in the incoming data.

[ray.wurlod]

Could it be that there are some non-printable characters in your data?