LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Identify and single out the record in a data set with max value in a specific field

    Employee
    Posted 01-16-2018 09:50

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

    Originally posted by: rbeliz000

    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.  RE: Identify and single out the record in a data set with max value in a specific field

    Employee
    Posted 01-16-2018 10:07

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

    Originally posted by: stonysmith

    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.



    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
    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.  RE: Identify and single out the record in a data set with max value in a specific field

    Employee
    Posted 01-16-2018 10:30

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

    Originally posted by: rbeliz000

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