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
- 9065 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 Partitioning ? where we can use Partition?
What is the need for an etl tool?
Hi All, I have three question. 1) How to remove the header and footer in a flat file in informatica? 2)How to load the first and last record of flat file into the target? 3)I have a Input and Output table. Input Name Salaray Month A 100 Jan A 200 Feb A 300 March B 400 April B 500 May B 600 June Output Name Salaray Month A 100 Jan A 300 Feb A 600 March B 400 April B 900 May B 1500 June How to implement and get the ouput as mentioned above. If Possible kindly provide the SQL query also. Kindly any one help to implement this concept. Thanks, Pradeep
What is the difference between SOURCE and TARGET BASED COMMITS?
wtite sql query following table are city gender no chennai male 40 chennai female 35 bangalore male 10 bangalore female 25 mumbai female 15 i want the output? city male female chennai 40 35 bangalore 10 25 mumbai null 15
how remove 1st 3 records & last 3 records in informatics
How we can get unique records into one target table and duplicate records into another target table??
11 Answers Deloitte, IBM, iGate,
What are roles and groups and benefits of using them?
What is aggregate awareness?
WHAT IS FACT TABLES?
I have some rows in source.I have to map half rows to one target and half rows to another target.i.e you wont know how many records in source.?Implement a mapping?
what is persistant cache in which situation we will use persistantcache
Teradata 
