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.  GroupMax, Count

    Employee
    Posted 05-01-2015 07:11

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

    Originally posted by: jpstory

    Hi All

    I am trying to find out the which file has the highest count of records that meet certain conditions.

    I have managed to obtain a list of file names and their individual count of records that met my conditions:

    FileName RdCnt
    FileA 200
    FileB 180
    FileC 150

    How to get the Agg node to only return the file that has the highest record count (200 in this case), instead of all files like the above?

    Here is what I currently put in the Agg node
    Script:
    RdCnt= count()
    emit 'FileName',RdCnt
    where lastInGroup

    GroupBy:
    'FileName'


  • 2.  RE: GroupMax, Count

    Employee
    Posted 05-01-2015 08:01

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

    Originally posted by: ryeh

    Something like this:
    node:Agg_Ex
bretype:core::Agg Ex
editor:sortkey=5543956340982a6d
input:@4b4668c040aa5a85/=
output:@4b4668e708143fb4/=
prop:GroupBy=<<EOX
1
EOX
prop:Script=<<EOX
n = groupMax('RdCnt')
emit n as MaxRdCnt
where lastInGroup

EOX
editor:XY=180,380
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
    You could also use the Quick Stats node. And also group by '1'. Unless there are multiple records per FileName. If you group by FileName, it will give you the maximum RdCnt within each FileName.


  • 3.  RE: GroupMax, Count

    Employee
    Posted 05-01-2015 08:13

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

    Originally posted by: ryeh

    Stony just brought to my attention that you probably care more about the FileName with the largest RdCnt. To do that, we could sort first by RdCnt, and the pull off the last item. So something like:

    node:Sort
bretype:core::Sort
editor:sortkey=5543985308f80ffc
input:@40fd2c743ebf4304/=Static_Data.40fe6c55598828e5
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
RdCnt
EOX
editor:XY=150,380
end:Sort

node:Get_Last
bretype:core::Tail
editor:Label=Get Last
editor:sortkey=5543987832553f8d
input:@40fd2c741aa333c7/=Sort.40fd2c746a2a3b47
output:@40fd2c747c310361/=
prop:RecordCount=1
editor:XY=260,380
end:Get_Last

node:Static_Data
bretype:core::Static Data
editor:sortkey=5543954e7039755a
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
FileName,RdCnt:int
FileA, 200
FileB, 180
FileC, 150
EOX
editor:XY=50,380
end:Static_Data
    Addendum: For large data sets where performance is important, sort in reverse and then use the Head node instead. To sort in reverse, i.e. largest number first, use reverse(RdCnt) in the Sort node. (Another Stony trick!)