Derivation Advise

[scorpion]

Hi All,

I am facing some issue to implement some simple logic in my job.

Could any one please help me on this.

i have source table with columns like"xxx","yyy","zzz"

and i have to populate to one of my target table column as per the below logic:


-populate "xxx"

-if value is not there for "xxx" then Populate "yyy"

-if value is not there for both "xxx" and "yyy" then populate "zzz"

-if value is not there for both "xxx" and "yyy" and "zzz" then leave blank.

Could any one please advise me the derivation to implement in transformer stage to achieve above logic

thanks in advance,

[DSguru2B]

Well, check for IsNotNull() for each column and if true, take that value. Something like

Code: Select all

If IsNotNull(col1) then col1 else if IsNotNull(col2) then col2 else if IsNotNull(col3) then col3 else ""

[scorpion]

Thanks Guru2B,

I implemented the logic sameway but i am getting onle column1 values and remaining are nulls.but not populating col2 vals or column 3 vals.

note:

i used NullToEmpty() function for thease columns in the first transformer.

is it giving the problem?

should i remove it?

tx

[us1aslam1us]

Yes, as you had already converted null values to empty that particular logic will not work. You need either count the characters of that field to implement your logic or do not use that nulltoempty() in the previous transformer.

[ccatania]

work backwards, meaning test for all 3 fields to be empty first,
then test for 2 then the 1.

if col1 isnull and col2 isnull and col3 isnull
then
if col1 = isnull and col2 is null
then values
else
if col1 isnull the value
else ''
else "

I think this should give you the results you are looking for, providing you know what value is in the fields, nulls, spaces..

[scorpion]

Hi ccatania,

Thanks for ur response and will be happy if you could give me the exact derivation using my columns like xxx,yyy,zzz(previously mentioned)

so that i can get solution.

for ex: Isnull(xxx)...like that


thanks
help would be appreciated

[scorpion]

hi us1aslam1us


when i tried to remove NullToEmpty function ,the rows are getting rejected.

from table it is extractiong 20000 rows ,but from transformer only 500 records are passing to next stage.

i am getting error like:

APT_CombinedOperatorController(0),0: Field 'AAA' from input dataset '0' is NULL. Record dropped.

Can any one through some light on this.

[us1aslam1us]

Give it a try to ccatania's logic without removing your nulltoempty function.

[Jewels]

Hey scorpion - Here's what I think you were looking for...

The way an Empty String is defined is with two single quotes and nothing in between. ('') This is what ccatania provided.

An Empty String is not the same as Null so a Null check doesn't work - the statement below is how to check for an Empty String value on the column you did the NullToEmpty function on.

If col1 = '' then ....

[Zhang Bo]

why you went so far away from the right solution?Dsguru2B gave you the right answer already,you just need one transformer stage,derivation is just what he showed you