Converting Rows to columns
I have Relational source like his.
JAN FEB MAR APR
100 200 300 400
500 600 700 800
900 100 200 300

I need to convert these rows into columns to the targe.

MONTH TOTAL
JAN 1500
FEB 900
MAR 1200
APR 1500

Please experts help me

Answers were Sorted based on User's Feedback



Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / sehajshangari1235

TAKE A NORMALIZER TRANSFORMATION.
CREATE A NORMALIZED PORT NAMED "DETAIL" WITH OCCURENCE 4
.CONNECT INPUT PORTS FROM SOURCE QUALIFIER TO EACH DETAIL
PORT IN NORMALIZER.

NEXT TAKE AN EXPRESSION TRANSFORMATION.IN THAT CREATE AN
OUTPUT PORT NAMED MONTH.AN IN EXPRESSION EDITER WRITE THE
LOGIC AS

DECODE(GCID_DETAIL,1,'JAN',DECODE(GCID_DETAIL,2,'FEB',DECODE(
GCID_DETAIL,3,'MARCH','APRIL')))
CONNECT THIS OUTPUT PORT TO TARGET TABLE PORT!!!




SEHAJ
7204719446

Is This Answer Correct ?    3 Yes 1 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / sham

we can't sum aggregate function in Exp T/F.i.e it is not
possible to use sum() in expression T/F. so go through Agg T/F
....

Group by month
o/p port-------> sum(sal)

Is This Answer Correct ?    3 Yes 2 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / reddy

Answer:

Source --> Source qualifier --> Normalizer --> Expr -->
Agg --> target

Step 1:

Source --> Source qualifier --> Normalizer --> Expr -->

Expression Condition:

DECODE(GCID_DETAIL,1,'JAN',DECODE(GCID_DETAIL,2,'FEB',DECODE
(
GCID_DETAIL,3,'MARCH','APRIL')))


JAN 100
JAN 500
JAN 900
FEB 200
FEB 600
FEB 100
MAR 300
MAR 700
MAR 200
APR 400
APR 800
ApR 300

Step 2:

Source --> Source qualifier --> Normalizer --> Expr -->
Agg --> target

1) Group by month
2) Sum(Amount)

MONTH TOTAL
JAN 1500
FEB 900
MAR 1200
APR 1500

Is This Answer Correct ?    1 Yes 0 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / prabhu

Source qualifier --> Normalizer --> Expr --> Agg --> target

In Normalizer give the four numeric values as input and then take numeric value and it's GK_value to expression.

In Expression i have made flag for month and hardcoding values of month

Flag_Jan --> IN(GK_Salary,1,5,9,0)
Flag_Feb --> in(GK_Salary,2,6,10,0)
Flag_Mar --> in(GK_Salary,3,7,11,0)
Month --> iif(Flag_Jan = 1,'Jan',iif(Flag_Feb = 1,'Feb',iif(Flag_Mar = 1,'Mar','Aprl')))

In agg group by on month and get sun(numeric value)

Pass month and sum(numeric) value to target

Is This Answer Correct ?    1 Yes 1 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / dilip ingole

using query

SELECT 'JAN' AS MONTHS,SUM(JAN) FROM CALADERQUERY
UNION
SELECT 'FEB' AS MONTHS,SUM(FEB) FROM CALADERQUERY
UNION
SELECT 'MAR' AS MONTHS,SUM(MAR) FROM CALADERQUERY
UNION
SELECT 'APR' AS MONTHS,SUM(APR) FROM CALADERQUERY;

Is This Answer Correct ?    0 Yes 0 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / siva

Please Explain me briefly. Thank You.

Is This Answer Correct ?    1 Yes 2 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / chandra mouli

You will take Expression Transfermation.
In Expression Editon you can write lke this
sum(months) and group by month.

Is This Answer Correct ?    2 Yes 5 No

Converting Rows to columns I have Relational source like his. JAN FEB MAR APR 100 200 300 400 50..

Answer / chandu621

you will take Expression transfermation
and write expression like this

sum(months)

Is This Answer Correct ?    0 Yes 7 No

Post New Answer

More Informatica Interview Questions

What is a filter transformation?

0 Answers  


What is source qualifier?

6 Answers   SDS,


If my source is having 30 million records, so obviously the cache could not be allocated with sufficient memory. What needs to be done in this case?

2 Answers   TCS,


How or for what purpose look up transformation would be useful in Sales or Banking Project? Please reply!

2 Answers  


hi all, i have 1 flat file having 10 rows. like:- id name amt 100 a 100 200 b 200 100 n 300 400 p 3000 100 a 100 600 x 6000 Now i want to load half of this data into one flat file and remaining in other flat file.like :- flat file 1 id name amt 100 a 100 200 b 200 100 n 300 ---------------------- ---------------------- flat file 2 id name amt 400 p 3000 100 a 100 600 x 6000 Plz reply me ASAP.

10 Answers  






what are 3 tech challenges/ common issues you face?

0 Answers   TCS,


how to connect two or more table with single source qualifier?

4 Answers   IBM,


What do you understand by a term domain?

0 Answers  


Using Connected or unconnected lookup for performance wise which one is preferable?.

1 Answers   Deloitte,


performance wise which one is better in joiner and lookup transformation

2 Answers  


How do we call shell scripts from informatica?

0 Answers  


Explain constraint based loading in informatica

0 Answers   Informatica,


Categories