Interested to Buy Any Domain ? << Click Here >> for more details...
Suppose we have a (assume relational) source table
Product_Id Month Sales
1 Jan x
1 Feb x
. . .
. . .
1 Dec x
2 Jan x
2 Feb x
. . .
. . .
2 Dec x
3 Jan x
3 Feb x
. . .
. . .
3 Dec x
. . .
. . .
and so on. Assume that there could be any number
of product keys and for each product key the sales
figures (denoted by 'x' are stored for each of the
12 months from Jan to Dec). So we want the result
in the target table in the following form.
Product_id Jan Feb March.. Dec
1 x x x x
2 x x x x
3 x x x x
.
.
So how will you design the ETL mapping for this case ,
explain in temrs of transformations.
- 5 Answers
- 10803 Views
- Accenture, VAMA, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / girish
Use an aggregator tx. Pass the ports (Product_id, Month,
Sales) to aggregator, group by Product_ID, manually create
12 ports for month like Jan, Feb, Mar, etc. Include an
expression for 12 ports, individually, as Jan -> IIF(Month
= Jan,Sales), Feb -> IIF(Month = Feb, Sales), etc.
Move these ports to the next transformation or to the
target. This should give the required output.
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / mukesh
Use an aggregator. Pass the ports (Product_id, Month,
Sales) to aggregator, group by Product_ID, manually create
12 ports for month like Jan, Feb, Mar, etc. Include an
expression for 12 ports,
Jan -> MAX(IIF(Month
= Jan,Sales)), Feb -> MAX(IIF(Month = Feb, Sales)), etc.
Without max, it will take last row..
Move these ports to the next transformation or to the
target. This should give the required output.
| Is This Answer Correct ? | 1 Yes | 1 No |
Answer / seekax
The reverse operation can be done using normalizer . . . To
carry out this process above mentioned we need to use
spliter in combination with joiner . . .
---- split using month -------- (into 12 sets of 2-columns)
1st SET 2nd SET
------- --------- . . . . .
product id,jan product id,jan
1,x 1,x
2,x 2,x
3,x 3,x
4,x 4,x . . . . . . .
---- join using product_id --------
Product_id Jan Feb March.. Dec
1 x x x x
2 x x x x
3 x x x x
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / janet
Simple SQL logic in the source qualifier sql will take care
of this issue.
Select product_id, month, sum(sales)
from source_table
group by product_id, month;
Then you can just pass the values straight through. No
need to split, aggregate, and join, etc.
| Is This Answer Correct ? | 4 Yes | 8 No |
What is main use of mapplet?
How to update a particular record in target with out running whole workflow?
 Code page compatibly –Where all changes are made to handle the scenario.
what is mapping optimization? wat are the techniques for tat
i have different sources in different databases ,that sources may be 100 tables i want to load these tables in to single target how to pearform the task
What happens if you increase commit intervals and also decrease commitExplain grouped cross tab?
Want to know about Training Centres for Informatica, Cognos and ETL Softwares in Mumbai, India.
What are the informatica performance improvement tips
My Source qualifier has empno, sal. Now my mapping is like SQ(EMPNO)->AGGR->EXP->TARGET SAL ------------>TARGET ? means only one source qualifier has 2 columns like empno, sal. connected to same target as shown in above mapping.Is this mapping valid or any issues are there if we design like this?
What are the new features in Informatica 5.0?
Hi i am new to Informatica. I have few question's in Informatica. Can any one respond, it will be appricaited. Q.Is Flat File Contains the Dynamic Cache?
Lookup transformation, one condition is having SQL override (Empno < 10) and the other condition is Lookup (Sal>1000), which is dynamic. How will u resolve this situation?
Teradata 
