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
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 |
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 |
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 |
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 |
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 |
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 |
how will u get 1 and 3rd and 5th records in table what is the query in oracle please help me
Hi experts, > I 'm having data like this and it's in flat file. > sno,name,marks,result > 1, ak, 80,p > 2, , ,20,f > 3,jack,55,p > > now in the second row a comma(,) is placed instead of name(char). > > when importing into SQ , in flat file wizard the num of columns increased to 5 instead of 4. > > could you tell me how to over come this issue.........
What are the methods for creating reusable transforamtions?
What is Collect performance data in informatica and what is the use of it.
How can you run a session without using server manager?
How we can create indexes after completing the loan process?
How can one identify whether mapping is correct or not without connecting session?
what is diff between IN and EXIT commands
what is semi-additive fact?where did u used?
Explain why we use partitioning the session in informatica?
What's the layout of parameter file (what does a parameter file contain?)?
Without source how to insert record to target?