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
- 5518 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 |
Why sorter is an active transformation?
Suppose we have a csv file with contents as below which is used has a source file. C1,C2,C3,C4 100,200,A B,300 200,400,X,Y,299 ---> it should be 4 values but by mistake 300,600,C D,566 with a comma in between X & Y,its 5. My target takes only 4 columns, How to take care of the 2 record as above without rejecting it and loading in the target. Hope u guys have understood my scenerio
why we r using presql&postsql in source qualifier?
How to go to the older version for a mapping?
which one is better performance wise joiner or look up
In Which scenario did u used Incremental loading?
What is the difference between Normal load and Bulk load?
which quality process u can approach in ur project
what is inline view?
My i/p is 1,2,3,4,5,6,7,8,9,10……….. o/p to be populate in two tables as below. o/p1: 10,20,30,40,50,60,70,80,90……. o/p2: 11,21,31,41,51,61,71,881,91…….
What is parameter file?
What is an MDDB? What is the difference between MDDBs and RDBMSs?
Teradata 
