I am hvaing SOURCE as
1000,null,null,null
null,2000,null,null
null,null,3000,null
null,null,null,4000

Now i want the OUTPUT as

1000,2000,3000,4000

For more clarification i want to elimate nulls and want in
a single line.


Please help me out

Answers were Sorted based on User's Feedback



I am hvaing SOURCE as 1000,null,null,null null,2000,null,null null,null,3000,null null,null,nul..

Answer / guest

Let suppose the data are as these fields
A B C D
1000,null,null,null
null,2000,null,null
null,null,3000,null
null,null,null,4000

In the mapping take A,B,C,D as input ports.set 4 output
ports for the same 4 input ports like out_A,out_B,out_C,out_D.

For out_A output port set value like-IIF(ISNULL(A),1000,A).
Similary for out_B-IIF(ISNULL(B),2000,B)
likewise for C,D.

if you don't want to hard code 1000,2000 in the expressions
written above just create 4 variable port like
var_A,Var_B,var_C,var_D before creating the output ports and
each variable port set max(A) for var_A,max(B) for var_B
port likely.

put each variable port name against 1000,2000,3000 and 4000
in respective expression.
as example for out_A set IIF(ISNULL(A),var_A,A)

Now take 4 output ports to a aggrerator transformation.set
group by any one port but not all ports at a time.

Join the ports to target.

Hurry you get result like this

1000,2000,3000,4000

Regards

Sukanta

Is This Answer Correct ?    2 Yes 0 No

I am hvaing SOURCE as 1000,null,null,null null,2000,null,null null,null,3000,null null,null,nul..

Answer / kondeti srinivas

it is so simple
write a query like this

SELECT MAX(A),MAX(B),MAX(C),MAX(D) FROM TABLE_NAME

Is This Answer Correct ?    3 Yes 1 No

I am hvaing SOURCE as 1000,null,null,null null,2000,null,null null,null,3000,null null,null,nul..

Answer / adisekhar

FROM THE ABOVE ANSWER WE HAVE TO ADD NORMALIZER TRANSFORMATION

Is This Answer Correct ?    1 Yes 0 No

I am hvaing SOURCE as 1000,null,null,null null,2000,null,null null,null,3000,null null,null,nul..

Answer / madhavi

In the lookup transformation->properties->presql condtion
write delete from table_name where column_name="NULL";.

Is This Answer Correct ?    1 Yes 1 No

I am hvaing SOURCE as 1000,null,null,null null,2000,null,null null,null,3000,null null,null,nul..

Answer / arunakumari l

let us take column names as col1,col2,col3,col4

STEP1:Add normalizer and place occurs value as 4

STEP2:use filter to eliminate null values

STEP3:take expression transformation and use concat
function to concatenate row values

i think this might work
tell me if i am wrong

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More Informatica Interview Questions

What is difference between stored procedure transformation and external procedure transformation?

2 Answers  


What do think which one is the better joiner or look up?

0 Answers  


Hi Experts, Performance Wise Which transformation is better in LOOKUP AND JOINER TRansformations? Why? Can anybody please explain it ? Please help me out from this Question. Thanks In Advance.

2 Answers   CTS,


what is song in infrmatica...?

0 Answers   CTS,


How to improve the performance of a session using sorter transformation?

0 Answers   Informatica,






Can yoU use the maping parameters or variables created in one maping into another maping?

2 Answers  


how to get the only updated records in the target

2 Answers  


What is informatica powercenter repository?

0 Answers  


expain about the tune parameters?

0 Answers   TCS,


I have 5 sessions s1,s2,s3,s4 & s5 and i want execute s1,s2,s3,s5 after s4. How?

8 Answers   DELL,


I have one source table and three target tables. When the session runs for the first time involving the mapping,i want that the loading should take place in 1st Target table only. The 2nd time the same session runs the loading should take place in 2nd Target only only and similarly when the session runs for the 3rd time loading should take place in Third only. And again when the session runs for the 4th time loading should take place in 1st Target table.

4 Answers   Cap Gemini, IBM,


Explain the various test procedures used to check whether the data is loaded in the backend, performance of the mapping, and quality of the data loaded in informatica?

0 Answers  


Categories