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

By using Filter Transformation,How to pass rows that does not satisfy the condition(discarded rows) to another target?

14 Answers   CSC, Emphasis,


how can we load 365 flat file to a single fact table (target) as a history load in single mapping?

2 Answers  


two types of data are there . one is mainframe and the other is ascii format . in informatica how can you get both the data in a single format in ascii .

2 Answers   TCS,


What are ETL Tools?

0 Answers   Informatica,


As union transformation gives UNION ALL output, how you will get the UNION output?

0 Answers   Informatica,






How i can Schdule the Informatica job in "Unix Corn Schduling tool" ?

2 Answers  


i have source as Column1 101 a 101 b 101 c so i want to load target as column1 ---> 101 abc

3 Answers   HP,


What are connected or unconnected transformations?

0 Answers   Informatica,


How to display session logs based upon particular dates. If I want to display session logs for 1 week from a particular date how can I do it without using unix. ?

0 Answers   CTS,


write a query following table bookid language 1234 english 1234 french 1234 spanish 1235 english i want the output are bookid lang1 lang2 lang3 1234 english french spanish 1235 english null null

1 Answers   TCS,


What is source qualifier transformation in informatica?

0 Answers  


we have 6 records in source , i need 2nd record in one target and 5th record in one target or 2nd & 5th record in same target.

8 Answers  






Categories