Results 1 to 3 of 3

Thread: Identify and single out the record in a data set with max value in a specific field

  1. #1

    Default Identify and single out the record in a data set with max value in a specific field

    I am looking for the simplest method to filter out the record with the max value in the iCount field.
    In the below example it would be the Mississippi record.
    any thoughts/suggestions would be much appreciated.
    node:Static_Data
    bretype:core::Static Data
    editor:sortkey=5a5dfc937db65ab8
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    state:string
    Mississippi
    Louisiana
    Pennsylvania

    EOX
    editor:XY=100,60
    end:Static_Data

    node:Filter
    bretype:core::Filter
    editor:sortkey=5a5dfcf471e21d5f
    input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX

    e=state.replace ("i","")
    L1=strlen (state)
    L2=strlen (e)
    iCount=L1-L2
    emit *, iCount



    EOX
    editor:XY=200,60
    end:Filter

    node:Output_Excel
    bretype:core::Output Excel
    editor:sortkey=5a5e10781087360f
    input:@49e34a5e55c82040/=Filter.40fd2c7420761db6
    prop:File=/lavastorm/data/RUNAREAS/xxxxxx/iCountfile.xlsx
    editor:XY=350,60
    end:Output_Excel

  2. #2
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    771

    Default

    A simple way is to sort by Icount then use an Agg to get the last record.
    However, this is not efficient for very large recordsets.
    If your input recordset is large, then it'd be better to sort by iCount.reverse() and use the HEAD node to pull the first record.



    Code:
    node:Agg
    bretype:core::Agg
    editor:sortkey=5a5e2f9757727634
    input:@40fd2c7427456e5b/=Sort.40fd2c746a2a3b47
    output:@40fd2c744c862db0/=
    prop:GroupBy=<<EOX
    1
    EOX
    editor:XY=310,290
    end:Agg
    
    node:Sort
    bretype:core::Sort
    editor:sortkey=5a5e2f914603192a
    input:@40fd2c743ebf4304/=Filter.40fd2c7420761db6
    output:@40fd2c746a2a3b47/=
    prop:CompareOrderExpr=<<EOX
    iCount
    EOX
    editor:XY=230,290
    end:Sort
    A slightly more direct way of doing it is to use an Agg instead of your original filter
    Code:
    node:Agg
    bretype:core::Agg
    editor:sortkey=5a5e309565ac035c
    input:@40fd2c7427456e5b/=Static_Data.40fe6c55598828e5
    output:@40fd2c744c862db0/=
    prop:GroupBy=<<EOX
    1
    EOX
    prop:Script=<<EOX
    if firstExec then {p = 0 s = ""}
    
    e=state.replace ("i","")
    L1=strlen (state)
    L2=strlen (e)
    iCount=L1-L2
    
    if iCount>p then {p=iCount s=State}
    
    emit s as State
    emit p as iCount
    where lastInGroup
    
    EOX
    editor:XY=310,290
    end:Agg

  3. #3

    Default

    thank you. i was trying to do this with the wrong agg_node.

Posting Permissions

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