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 |
Which means the first record should come as last record and last record should come as first record and load into the target file?
what is the predefined port in dynamic lookup
Source is a flat file and want to load unique and duplicate records separately into two separate targets; right??
By using Filter Transformation,How to pass rows that does not satisfy the condition(discarded rows) to another target?
If I have 10 flat files with same name abc.txt files with different timestamps as source I need to load them in tgt table oracle. in between job execution fails and rows are not loaded into tgt. how can I make them load in that target even if my job fails?
How do you migrate data from one environment to another?
What is the Difference between sorter and aggregator?
I have the input file as col1 col2 col3 3 2 1 7 6 8 I should get the output as col1 col2 col3 1 2 3 6 7 8 ....What is the logic to get this? Is there any transformation which sorts row wise ? If not how to sort the incoming records row wise?
How to create a mapping ? id date 101 2/4/2008 101 4/4/2008 102 6/4/2008 102 4/4/2008 103 4/4/2008 104 8/4/2008 O/P - shud have only one id with the min(date) How to create a mapping for this
What is standalone command task?
waht is dataware house
What is galaxy shema