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
- 5574 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 fact table?
PLS REAL TIMERS IAM WAITING FOR FAVOROBLE REPLY..REGARDING ETL TESTING INFORMATION...ETL TESTER NEEDS ETL DEVOLPMENT KNOWLEDGE OR NOT..?. IF HE NEED DEVLPMNT KNOW WHAT HE LEANR FIRST... AS A DEVOLOPER U KNOW THE ETL TESTING PROCESS.. PLS EXPLAIN IN DETAIL THANK U
how can we run workflow with pmcmd?
COL1,COL2 ABC,1 XYZ,2 HERE IN COL2 VALUES 1,2 NOT STSANDARD(IE MEANS NOT FIXED VALUES LIKE OTHER SOME VALUES LIKE 10,20) O/P IS COL1,COL2 ABC,2 XYZ,1
Can anyone briefly describe abt audit table loading?
what is the diff b/w cached and unchaed look ups
I have in my source Records like 100,101,102 etc. I have router transformation which has groups like one group is empno=100, second group is empno>99. Now i want to know my source record 100 loads into which group?
What is the difference between bitmap and btree index?
How to extract original records at one target & Duplicate records at one target? {with out using EXPRESION transformation and sequence generator transformation?}<-this is important
There are 4 flat files with number of records as indicated below. Which files should be picked first for joining using joiners so as to get best performance. File A - 1000 records File B - 100 records File c - 10000 records File D - 10 records Please explain. Thanks and Regards,
What is a data modeling?
what we require for D.modelling?
Teradata 
