I have id, seq_no date, bill_amt and weight
1,11,'01-Jan-2014',100,2
2,12,'01-Jan-2014',40,5
3,13,'01-Jan-2014',32,5
4,14,'01-Jan-2014',98,2
5,15,'01-Jan-2014',105,3
6,16,'01-Jan-2014',11,3

1,11,'02-Jan-2014',40,2
2,12,'02-Jan-2014',100,5
3,13,'02-Jan-2014',132,5
4,14,'02-Jan-2014',198,2
5,15,'02-Jan-2014',15,3
6,16,'02-Jan-2014',16,3

Now I need output as
date MXAMT_LSTWGHT MINAMT_GRTWGHT
01-Jan-2014 100 32
02-Jan-2014 198 100

Could not think of mapping to do so. Please help



I have id, seq_no date, bill_amt and weight 1,11,'01-Jan-2014',100,2 2,12,'01-Jan-2..

Answer / venkatesan r

Follow below steps:

Step1: Agreegate data based on "Date", fetch Min and Max of
Weight.

date MAX(Weight) Min(Weight)
1-Jan-14 5 2
2-Jan-14 5 2


Step2: Join with the source data using Date

seq_no date bill_amt weight MAX(Weight)
Min(Weight)
11 1-Jan-14 100 2 5 2
14 1-Jan-14 98 2 5 2
15 1-Jan-14 105 3 5 2
16 1-Jan-14 11 3 5 2
12 1-Jan-14 40 5 5 2
13 1-Jan-14 32 5 5 2
11 2-Jan-14 40 2 5 2
14 2-Jan-14 198 2 5 2
15 2-Jan-14 15 3 5 2
16 2-Jan-14 16 3 5 2
12 2-Jan-14 100 5 5 2
13 2-Jan-14 132 5 5 2

3. Using expression, create 2 flags.
a, Flag1 - iif(Weight=min(weight),Bill_amt,NULL)
b, Flag2 - iif(Weight=max(weight),Bill_amt,NULL)

seq_no date bill_amt weight MAX(Weight)
Min(Weight) Flag1 - iif(Weight=min
(weight),Bill_amt,NULL) Flag2 - iif(Weight=max
(weight),Bill_amt,NULL)
11 1-Jan-14 100 2 5 2 100

14 1-Jan-14 98 2 5 2 98

15 1-Jan-14 105 3 5 2

16 1-Jan-14 11 3 5 2

12 1-Jan-14 40 5 5 2
40
13 1-Jan-14 32 5 5 2
32
11 2-Jan-14 40 2 5 2 40

14 2-Jan-14 198 2 5 2 198

15 2-Jan-14 15 3 5 2

16 2-Jan-14 16 3 5 2

12 2-Jan-14 100 5 5 2
100
13 2-Jan-14 132 5 5 2
132

4.Agregate Max(Flag1) and min (Flag2) group by Date
And you get the Splution,

date Min_Flag_flag Max_flag
1-Jan-14 100 32
2-Jan-14 198 100

Need help, Ping me

Is This Answer Correct ?    3 Yes 3 No

Post New Answer

More Informatica Interview Questions

In dept 10, 5 emp sal, i want to do sum of dept 10 emp sal. Is there any othere transformation than the aggrator? if yes how?

5 Answers   Patni,


my session have to run Monday to Saturday not run sunday how to sedule in informatica level?

1 Answers   Infinite Computer Solutions,


Target is zero file and how can you send the email?

2 Answers   Bank Of America,


Suppose on 1st Nov 2010 you had created a mapping which includes huge aggregator calculations and it is under process for next two days. You will notice that even on 3rd day also its still calculating. So without changing a logic or changing a mapping How will you troubleshot or to run that mapping? Explain the steps

0 Answers  


Explain what transformation and how many are there in informatica?

0 Answers  






How You Pull the records on daily basis into your ETL Server.

2 Answers   TCS,


To achieve the session partition what r the necessary tasks u have to do?

1 Answers  


Could any one to tell Fact table Partitioning?

2 Answers  


What is rank transformation?

0 Answers  


what is incremantal aggregation?

1 Answers  


How to load dimension and fact in same mapping at same time.explain me the logic used for that scenario.

1 Answers   IBM,


While importing the relational source defintion from database,what are the meta data of source U import?

1 Answers  


Categories