LAE

 View Only

  • 1.  Maximum in Agg node

    Employee
    Posted 07-11-2010 15:37

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: TheBishop

    Hi,

    Is it possible to use the Agg node to report the maximum value in a group in the same way you can sum the values in a group.

    Is there a reason why max wouldn't work in the Agg node? Here is what I am using

    testmax = max(Column)
    testsum = sum(Column)

    testmax is reporting the value of Column for each record (expected the running Maximum)
    testsum is reporting the sum of the value of Column (as expected)

    Testmax Testsum Column
    10.........10.........10
    1...........11.........1
    2...........13.........2

    Any help would be appreciated.


  • 2.  RE: Maximum in Agg node

    Employee
    Posted 07-11-2010 22:47

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: alyon

    Hi,

    In the aggregate node, put following code in Script section:

    testmax = groupMax(id)
    testsum = sum(id)

    emit *, testmax, testsum


    And put following in GroupBy section:
    true



    This should help you acheieve what you are looking for.

    Cheers
    Anshul


  • 3.  RE: Maximum in Agg node

    Employee
    Posted 07-12-2010 07:40

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    If you are expecting a single row with the Max and Sum values, then it needs to be

    testmax = groupMax(id)
    testsum = sum(id)

    emit testmax, testsum
    where lastInGroup


    Without the lastInGroup condition, you'll get out the same number of records as you send in


    Originally posted by: alyon
					


    
In the aggregate node, put following code in Script section:

    
testmax = groupMax(id)
    
testsum = sum(id)

    
emit *, testmax, testsum 


    
And put following in GroupBy section:
    
true



    
This should help you acheieve what you are looking for.

    
Cheers
    
Anshul


  • 4.  RE: Maximum in Agg node

    Employee
    Posted 07-12-2010 20:45

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: TheBishop

    @Anshul
    Thanks! Thats exactly what I needed.


  • 5.  RE: Maximum in Agg node

    Employee
    Posted 12-21-2018 08:36

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Arunn

    Hi,

    I want to group by the data based on the fields and get the values of non-key fields based on the latest date.

    Input :

    AcctNo DeptNo Amt Balance Bal_Date
    100 Mech 1000 800 01-Nov-2018
    100 Mech 2000 500 02-Nov-2018
    100 Mech 1000 800 03-Nov-2018
    200 Civil 5000 500 01-Nov-2018
    200 Civil 1000 800 02-Nov-2018
    200 Civil 3000 300 03-Nov-2018
    300 IT 7000 1000 01-Nov-2018
    300 IT 8000 3000 02-Nov-2018

    I want to group by based on the fields AcctNo, DeptNo and sum of Amt field and latest values of Balance & Bal_Date.

    Expected Output :

    AcctNo DeptNo SUM(Amt) Balance Bal_Date
    100 Mech 4000 800 03-Nov-2018
    200 Civil 9000 300 03-Nov-2018
    300 IT 15000 3000 02-Nov-2018


  • 6.  RE: Maximum in Agg node

    Employee
    Posted 12-21-2018 11:33

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: gmullin

    Try this:

    node:Static_Data
bretype:core::Static Data
editor:sortkey=5c1d2e2707df0f50
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
AcctNo,DeptNo,Amt:int,Balance:int,Bal_Date
100,Mech,1000,800,01-Nov-2018
100,Mech,2000,500,02-Nov-2018
100,Mech,1000,800,03-Nov-2018
200,Civil,5000,500,01-Nov-2018
200,Civil,1000,800,02-Nov-2018
200,Civil,3000,300,03-Nov-2018
300,IT,7000,1000,01-Nov-2018
300,IT,8000,3000,02-Nov-2018
EOX
editor:XY=210,200
end:Static_Data

node:Balance_Date
bretype:core::Filter
editor:Label=Balance Date
editor:sortkey=5c1d2e57014e2b20
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
_balDate = 'Bal_Date'.date("DD-m-CCYY")

emit *
override emit _balDate as "Bal_Date"
EOX
editor:XY=310,200
end:Balance_Date

node:Sort
bretype:core::Sort
editor:sortkey=5c1d2e3d1bbd4f68
input:@40fd2c743ebf4304/=Balance_Date.40fd2c7420761db6
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
'AcctNo','Bal_Date'
EOX
editor:XY=410,200
end:Sort

node:Agg_Ex
bretype:core::Agg Ex
editor:sortkey=5c1d2ef04d1719a5
input:@4b4668c040aa5a85/=Sort.40fd2c746a2a3b47
output:@4b4668e708143fb4/=
prop:GroupBy=<<EOX
'AcctNo'
EOX
prop:Script=<<EOX
_total = groupSum('Amt')

emit 'AcctNo','DeptNo',_total as "Sum(Amt)",'Balance','Bal_Date'
where lastInGroup

EOX
prop:SortInput=false
editor:XY=530,200
node:Bypass
bretype:::Bypass
editor:shadow=4b467f7e02db3a85
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4b467f8972dc33df
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4b467f9b3d5028c0
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:Agg_Ex


  • 7.  RE: Maximum in Agg node

    Employee
    Posted 12-24-2018 11:02

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Arunn

    Thanks. I tried this logic but it's not working for my requirement as I'm using more than one columns as a key in aggregation.. My requirement is fetch the latest record for a month based on the date.. I'm using the col1,col2,col3,col4,date as a key in Sort node.. It's giving the correct order and then I'm connected the output to Agg Ex node in which I've given the col1,col2,col3,col4 as a key without date (Want to aggregate for a month) so here the order get changed and not getting the latest record(non-key values) for a month..