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
- 5670 Views
- Axtria, I also Faced
- E-Mail Answers
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 |
What is the method of seperating unique and duplicate records in session level?
2 Answers Emphasis, JBL, Wipro,
From where you extract the data, how you did it into informatica? explain....
1 Answers Accenture, Unisoft Infotech,
How to prepared lld.can any one tell me detailed explanetion.
If we set dd_insert in mapping and Delete in session properties what will happen.?
What is domain and gateway node?
How to do Integration testing in Informatica?
There is a table with emp salary column how to get the fields belongs to the salary greater than the average salary of particular department. Write a query
if i am having 10 records in source, i want 20 records in target...how will you do it
When do we use dynamic cache and static cache in connected and unconnected lookup transformations?
Define joiner transformation?
Explain the tuning lookup transformation - informatica
How you prepared reports for OLAP?
Teradata 
