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 |
i have a source which relational.I am trying to populate to target flat file with one column for daily date which is sysdate. I want to populate the sysdate coulmn with DD/MM/YYYY format. kindly provide a solution for this..my clear that my target is flat file.
a flat file having 1 laks records. if i want convert excel file what happen. (bcz a exccel sheet having 65536 colums but flat files one lak columns). how to get one laks colums in excel sheet .
how much memory (size) occupied by a session at runtime
what is data modelling? what are types of modelling?In which suituation will use each one?
write sql query vertical to horiozontal following table id name 1 100 2 dinesh 3 india 1 101 2 suresh 3 india 1 103 2 prakesh 3 usa i want output like id name country 100 dinesh india 101 suresh india 103 prakesh usa
Where do we use mapping variables in informatica?
what is the flat file partition and where have to specify it
what is the difference between copy object import objects using repositery manager which one is best
what is threshold error in informatica?
1)you have multiple source system where u receive files ,how do you actually load into mapping using transformation,what are the transformation you use? 2)you have files in ftp location ,how do you get it into mapping with you ETL concept?
in a table it has 200 rows the query is select 150 from tablename.whats the output
generate Unique sequence numbers for each partition in session with Unconnected Lookup ? Hi All, Please help me to resolve the below issue while Applying partitioning concept to my Session. This is a very simple mapping with Source, Lookup , router, and target. I need to Lookup on the target and compare with the source data, if any piece of data is new then Insert, and If any thing change in the existed data then Update. while Inserting the new records to the target table I'm generating sequence numbers with Unconnected lookup, by calling the maximum PK ID from the target table. The above flow is working fine from last one year. Now I wish to apply the Partitioning concept to the above floe(session) At source I used 4 pass through partitions.(For Each partition different filter conditions to pull the data from source) at Target I used 4 passthrough Partitions. it is working fine for some data, but for some rows for Insert Operation , it is throwing Unique key errors, because while Inserting the data it is generating the same sequence key twice. In detail : 1st row is coming from 1st partition and generated the sequence number 1 for that row. 2nd row is coming from 1st partition and generated the sequence number 2 for that row 3rd row is coming from the 2nd partition generated the sequence number 2 again for that row. (it must generate 3 for this row) the issue is becuase of generating the same sequence numbers twice for different partitions. Can any one Please help me to resolve this issue. While Applying partitions how can I generate a Unique Sequence numbers from Unconnected lookup for Each partitioned data. Regrads, N Kiran.