Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 1 Answers
- 5575 Views
- Axtria, I also Faced
- E-Mail Answers
Answer Posted / 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 View All Answers
Give one example for each of conditional aggregation, non-aggregate expression, and nested aggregation?
How to delete duplicate row using informatica?
Write the advantages of partitioning a session?
What is sq transformation?
explain one complex mapping with logic? sales project?
Briefly define a session task?
How to load the data from people soft hrm to people soft erm using informatica?
draw informatica architecture 8.6 ?
Explain the different kinds of facts.
can we override a native sql query within informatica? Where do we do it?
Explain direct and indirect flat file loading (source file type) - informatica
Where is metadata stored?
How to update source definition?
How to call shell scripts from informatica?
What is a grid in Informatica?
