I have source like this
year account month amount
----- --------- ------ --------
1999 salaries jan 9600
1999 salaries feb 2000
1999 salaries mar 2500
2001 benfits jan 3000
2001 benfits feb 3500
2001 benfits mar 4000
-->i need target like this
year account month1 month2 month3
----- --------- -------- -------- --------
1999 salaries 9600 2000 2500
2001 benfits 3000 3500 4000
Answers were Sorted based on User's Feedback
Answer / soumen
Use following method --
sq_source --> exp_tans --> agg_trans --> target
exp_trans --> add 3 variable --
v_month1 == iif(month='jan', amount, 0)
v_month2 == iif(month='feb', amount, 0)
v_month3 == iif(month='mar', amount, 0)
agg_trans --> add 5 cols from exp_trans --
year(check group by), account(check group by), v_month1,
v_month2, v_month2
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / soumen
Little corrections ---->
Use following method --
sq_source --> exp_tans --> agg_trans --> target
exp_trans --> add 3 variable --
v_month1 == iif(month='jan', amount, 0)
v_month2 == iif(month='feb', amount, 0)
v_month3 == iif(month='mar', amount, 0)
agg_trans --> add 5 cols from exp_trans --
year(check group by), account(check group by), sum(v_month1),
sum(v_month2), sum(v_month2)
Now forward these 5 cols to Target.
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / sai karthik
We can achieve this with Aggregator transformation.
SQ-->AGG-->TGT
IN AGGREGATOR TRANSFORMATION GROUP BY YEAR AND ACCOUNT PORTS
AND TAKE THREE OUTPUT PORTS 1. MONTH1, 2. MONTH2, 3. MONTH3 AND WRITE THESE FUNCTIONS.
1. MAX(DECODE(MONTH,'JAN',AMOUNT))
2. MAX(DECODE(MONTH,'FEB',AMOUNT))
3. MAX(DECODE(MONTH,'MAR',AMOUNT))
CONNECT THE PORTS TO THE TARGET AND RUN THE SESSION AND SEE THE RESULT.
CHEERS
SAI KARTHIK
Is This Answer Correct ? | 1 Yes | 0 No |
write a query for how to eliminate the duplicate rows without using distinct?
How can we improve session performance in aggregator transformation?
1)What is Data Masking in informatica? 2)How to restrict junk characters while loading data into target. 3) what happen if i won't pass sorted input in a transformation.
why normalizer transformation can not used in mapplet? plzzzz answer
Is LOAD MANAGER AND LOAD BALANCER same in informatica?
What is the cumulative sum and moving sum?
If no. of source columns is changing every time (First time it is 10 next time it is 20 so on). How to deal with it without changing mapping?
What is the grain level in DWH? If i have year to day. what is the grain level of DWH from year to day.
WHat all joins are there in Source Qualifier?
i have n number of records in my source, i want first and last record to my target. how can u implement this scenario .
How to load the source table into flat file target(with columns) in informatica?
Explain constraint based loading in informatica