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 are slowly changing dimensions?

5 Answers   Verinon Technology Solutions, Informatica,


where to select code page option?

2 Answers   TCS,


What are Business Components in Informatica?

1 Answers  


What is hash partition?

2 Answers  


Update strategy transformation is an active transformation.How it changes the number of records that pass through it? Please explain....

2 Answers  






what is the drillup & drill down?and use of the drill up and drill down?

1 Answers   IBM,


what is the diff b/w target load plan and cbl?

3 Answers   iGate, CTS,


How to elaborate powercenter integration service?

0 Answers  


Which gives the more performance when compare to fixed width and delimited file ? and why?

3 Answers   HP,


I have 1 crore record in my table and I have to load 25 L in 1st target,25L in 2nd target and 25L in 3rd target? I am new to Informatica,can anybody suggest me the idea?

2 Answers  


hi all my source looking like below column1 column2 101,102,103 abc,def,ghi 1001,1002,1003 a,b,c i want my target is column1 column1 101 abc 102 def 103 ghi 1001 a 1002 b 1003 c any one can you help

1 Answers  


How to extract original records at one target & Duplicate records at one target?

12 Answers  






Categories