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.  Counting occurrences, but only if sequential

    Employee
    Posted 10-29-2015 10:39

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

    Originally posted by: peterr

    Hi,

    Here is the scenario I am wanting to resolve:

    Report Period Reel Id Reel Provision
    2015-06 1 Yes
    2015-07 1 No
    2015-08 1 Yes
    2015-09 1 Yes

    My outcome is to know if the reel provision is New i.e. it this it the first time this reel has been provisioned, or "old" = the reel was also provisioned in the previous period.

    Report Period Reel Id Reel Provision Provision New or Old?
    2015-06 1 Yes New
    2015-07 1 No
    2015-08 1 Yes New
    2015-09 1 Yes Old

    What I had done is simply filter the report to only have reels that had a provision, and then do a count, with a simple logic that if count = 1 it's New, anything greater than 1 = Old

    Report Period Reel Id Reel Provision Provision New or Old? Count Occurrences
    2015-06 1 Yes New 1
    2015-07 1 No
    2015-08 1 Yes New 2
    2015-09 1 Yes Old 3

    ... so in 2015-08 this return a count of 2 = Old when it is actually New. So my approach to solve this is to somehow we sent the count anytime there is sequential gap... but no idea how to do this?

    Any suggestions on how to resolve this would be gratefully received.

    Thanks

    Peter


  • 2.  RE: Counting occurrences, but only if sequential

    Employee
    Posted 10-29-2015 11:07

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

    Originally posted by: ryeh

    Copy the following into Lavastorm. I think it's easier to check with if/then logic, and keeping track of the previous provision status.

    node:BRD_Data
bretype:core::Static Data
editor:Label=ReportPeriod, ReelId, Prov
editor:sortkey=56325d20224b0481
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ReportPeriod:string,ReelId:string,Prov:string
2015-05,1,No
2015-06,1,Yes
2015-07,1,No
2015-08,1,Yes
2015-09,1,Yes
2015-10,1,Yes
2015-11,1,Yes
2015-12,1,No
2016-01,1,Yes
EOX
editor:XY=160,300
end:BRD_Data

node:NewOld
bretype:core::Filter
editor:Label=New/Old
editor:sortkey=56325d29327d0866
input:@40fd2c74167f1ca2/=BRD_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec
then PrevProv = Prov

if firstExec and Prov == "Yes"
then NewOld = "New"
else if Prov == "No"
then NewOld = ""
else if PrevProv == "No" # assuming that Prov is either Yes or No
then NewOld = "New"
else NewOld = "Old"

PrevProv = Prov
	

emit *,NewOld

EOX
editor:XY=330,300
end:NewOld


  • 3.  RE: Counting occurrences, but only if sequential

    Employee
    Posted 10-30-2015 04:54

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

    Originally posted by: peterr

    Hi Ryeh,

    Thanks for this!... but I have one last problem that I can't figure out (sorry, I'm very new to Lavastorm, my background is accounting and excel, so still getting my head around some of the coding logic... but getting there).

    One extra part I need to add is to also add the reel id to this logic. In the example below the first reel in the list is Reel 2 and Provision = Yes, so this returns NewOld as "New" = correct. The very next reel in the list is Reel Id 1, Provision = Yes, and since this is the first time Reel Id 1 appears in the list it should also be "New", but since the current code only looks at the Prov it gets flagged as "Old".... so how do I extend the existing logic to also check if this is the same reel id, or a new reel id?

    Thanks in advance

    Peter

    node:BRD_Data
    bretype:core::Static Data
    editor:Label=ReportPeriod, ReelId, Prov
    editor:sortkey=56325d20224b0481
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    ReportPeriod:string,ReelId:string,Prov:string
    2015-05,2,Yes
    2015-06,1,Yes
    2015-07,1,No
    2015-08,1,Yes
    2015-09,1,Yes
    2015-10,1,Yes
    2015-11,1,Yes
    2015-12,1,No
    2016-01,1,Yes
    EOX
    editor:XY=160,300
    end:BRD_Data

    node:NewOld
    bretype:core::Filter
    editor:Label=New/Old
    editor:sortkey=56325d29327d0866
    input:@40fd2c74167f1ca2/=BRD_Data.40fe6c55598828e5
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX
    if firstExec
    then PrevProv = Prov

    if firstExec and Prov == "Yes"
    then NewOld = "New"
    else if Prov == "No"
    then NewOld = ""
    else if PrevProv == "No" # assuming that Prov is either Yes or No
    then NewOld = "New"
    else NewOld = "Old"

    PrevProv = Prov


    emit *,NewOld

    EOX
    editor:XY=330,300
    end:NewOld


  • 4.  RE: Counting occurrences, but only if sequential

    Employee
    Posted 10-30-2015 06:07

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

    Originally posted by: ryeh

    That's an easy fix! Use an Agg node and group by ReelID.

    node:New_Old_by_Group
bretype:core::Agg Ex
editor:Label=New Old by Group
editor:sortkey=56336a542e3476e6
input:@4b4668c040aa5a85/=ReportPeriod_ReelId_Prov.40fe6c55598828e5
output:@4b4668e708143fb4/=
prop:GroupBy=<<EOX
ReelId
EOX
prop:Script=<<EOX
if firstInGroup
then PrevProv = Prov

if firstInGroup and Prov == "Yes"
then NewOld = "New"
else if Prov == "No"
then NewOld = ""
else if PrevProv == "No" # assuming that Prov is either Yes or No
then NewOld = "New"
else NewOld = "Old"

PrevProv = Prov


emit *,NewOld


EOX
editor:XY=350,440
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:New_Old_by_Group

node:NewOld_2
bretype:core::Filter
editor:Label=New/Old
editor:sortkey=56325d29327d0866_2
input:@40fd2c74167f1ca2/=ReportPeriod_ReelId_Prov.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec
then PrevProv = Prov

if firstExec and Prov == "Yes"
then NewOld = "New"
else if Prov == "No"
then NewOld = ""
else if PrevProv == "No" # assuming that Prov is either Yes or No
then NewOld = "New"
else NewOld = "Old"

PrevProv = Prov


emit *,NewOld

EOX
editor:XY=350,300
end:NewOld_2

node:ReportPeriod_ReelId_Prov
bretype:core::Static Data
editor:Label=ReportPeriod, ReelId, Prov
editor:sortkey=56325d20224b0481_2
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
ReportPeriod:string,ReelId:string,Prov:string
2015-05,2,Yes
2015-06,1,Yes
2015-07,1,No
2015-08,1,Yes
2015-09,1,Yes
2015-10,1,Yes
2015-11,1,Yes
2015-12,1,No
2016-01,1,Yes
EOX
editor:XY=140,390
end:ReportPeriod_ReelId_Prov


  • 5.  RE: Counting occurrences, but only if sequential

    Employee
    Posted 10-30-2015 11:57

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

    Originally posted by: peterr

    !Hi Ryeh,

    That's perfect. Thanks for your help!

    Peter