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 |
what are all the deliverables of ETL informatica power center project? what are all updated or created by etl developer as his everyday activities?
what is the size of u r project? and what is the size of source file?
Explain the flow of data in Iinformatica?
My source is like bellow F.NAME M.NAME L.NAME A B C D E F G H I and out put should like S.NO F.NAME M.NAME L.NAME 1 A B C 2 D E F 3 G H I 4 UNNOWN UNNOWN UNNOWN How can we acheive? please explane me indetail
Hi, Can anyone just give me the list of all versions of Informatica ? Advance Thanks
I want my deployment group to refer an external configuration file, while i deploy in the production environment. How can i achieve it.
write a query for how to eliminate the duplicate rows without using distinct?
what is the diff b/w target load plan and cbl?
2,can we insert duplicate data with dynamic look up cache,if yes than why and if no why?
I am getting five sources in a day and i donot know when i get them. i need to load data into the target and run the session. but here i can't keep the session in running or can't stop the session. plz help me
3,if our source containing 1 terabyte data so while loading data into target what are the thing we keep in mind?
In what scenario we use to improve session performance by pushdown optimization?can any one give example?