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
- 5519 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
How to create Target definition for flat files?
I have two different source structure tables, but I want to load into single target table? How do I go about it? Explain in detail through mapping flow.
what is the size of u r source(like file or table)?
Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.
What is fact table? Explain the different kinds of facts.
How many numbers of sessions can one group in batches?
What is the reusable transformation?
can any one give some examples for pre sql and post sql(Except dropping and creating index).
hi real timers . iam waiting for ur reply regarding ETL TESTING
What is lookup transformation?
What is data transformation manager process?
What are batches?
what is the difference between informatica6.1 and infomatica7.1
To Provide Support For Mainframes Source Data, which Files Are Used As A Source Definitions?
Mention a few design and development best practices for informatica?
