I have areuqirement like below.
I need to find the max value by grouping on two columns.If even if i have a negative value but i need to take absolute value but in the output i need to populate originaal value.
Input data:
100,200,-5
100,200,1
100,200,-4
Output :
100,200,-5
Thanks
Need Aggregation logic in transformer
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
You need to ensure sorted data by your defined keys and ensure that your partitioning supports that sorted order. When all that is complete then you can create one or as many stage varibles as needed to kep track of your keys and stage varibles for your aggregates or max-values. Then just let datastage process your data only letting the last record of each group go through the transformer carrying the aggregate values. Set changedrec key at your sort to simplify this process and make sure it defines the last record in your group...
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
You need to ensure sorted data by your defined keys and ensure that your partitioning supports that sorted order. When all that is complete then you can create one or as many stage varibles as needed to kep track of your keys and stage varibles for your aggregates or max-values. Then just let datastage process your data only letting the last record of each group go through the transformer carrying the aggregate values. Set changedrec key at your sort to simplify this process and make sure it defines the last record in your group...
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
You need to ensure sorted data by your defined keys and ensure that your partitioning supports that sorted order. When all that is complete then you can create one or as many stage varibles as needed to kep track of your keys and stage varibles for your aggregates or max-values. Then just let datastage process your data only letting the last record of each group go through the transformer carrying the aggregate values. Set changedrec key at your sort to simplify this process and make sure it defines the last record in your group...
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
Simple example
IsSame = If LastKey = InStream.Key Then Yes Else No
LastKey = InStream.Key
Counter = If IsSame = Yes Then Counter + 1 Else 0
ValueCount = If IsSame = Yes Then ValueCount + InStream.Value Else 0
MaxValue =
If IsSame = Yes Then
If MaxValue < InStream.MaxValue Then InStream.MaxValue
Else MaxValue
Else -999
In Your Output Stream, Put Filter Option
If IsSame = Yes And LastRec = 1 Then 1 Else 0
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
Sorry about the multiple posting ![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles