Results 1 to 7 of 7

Thread: Maximum in Agg node

  1. #1

    Default Maximum in Agg node

    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. #2

    Default

    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. #3
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    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


    Quote Originally Posted by alyon View Post

    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. #4

    Smile

    @Anshul
    Thanks! Thats exactly what I needed.

  5. #5

    Default

    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. #6
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    183

    Default

    Try this:

    Code:
    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. #7

    Default

    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..

Similar Threads

  1. How to obtain maximum length from columns
    By matt.mac in forum Nodes
    Replies: 1
    Last Post: 07-22-2010, 06:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •