I have source like this year account month amount ----- --------- ------ -----

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

Question Posted / muni123

4 Answers
9190 Views
HCL, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

I have source like this year account month amount ----- --------- ------ -------- 1999 salaries j..

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

I have source like this year account month amount ----- --------- ------ -------- 1999 salaries j..

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

I have source like this year account month amount ----- --------- ------ -------- 1999 salaries j..

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

I have source like this year account month amount ----- --------- ------ -------- 1999 salaries j..

Answer / a

select yr,acc, max(decode (mon,'jan',am)) mon1,max(decode
(mon,'feb',am)) mon1,max(decode (mon,'mar',am)) mon1 from
intr
group by yr,acc

Is This Answer Correct ?

1 Yes

2 No

Post New Answer