Results 1 to 4 of 4

Thread: Calculate end date from next row

  1. #1
    Contributor
    Join Date
    Apr 2011
    Location
    Copenhagen
    Posts
    144

    Default Calculate end date from next row

    Hi Guys.

    Is there a way to accomplice this?:
    For every offering I want to calculate the ToDate depending on what the next rows validity date unless it is the last row in group, then it should just be "31-12-2013"

    node:BRD_Data
    bretype:core::Static Data
    editor:Label=BRD Data
    editor:sortkey=5b3206a2190e3cab
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    Offering id,Price validity from date,ToDate
    Dummy,04-04-2018,06-04-2018
    Dummy,07-04-2018,11-04-2018
    Dummy,12-04-2018,18-04-2018
    Dummy,19-04-2018,31-12-2030
    Dummy2,02-04-2018,04-04-2018
    Dummy2,05-04-2018,09-04-2018
    Dummy2,10-04-2018,16-04-2018
    Dummy2,17-04-2018,31-12-2030

    EOX
    editor:XY=480,110
    end:BRD_Data


    Thank you in advance.

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

    Default

    This uses a subject called "Persistence".. it is possible for data to be placed in a variable, and then held until the next (or some later) record.
    The trick in this case is to sort from youngest to oldest.. with the earliest date at the bottom.
    Set the "ending" value, and then as each record is processed, hold onto it's value till the next record.

    Code:
    node:Agg
    bretype:core::Agg
    editor:sortkey=5b325335137653cc
    input:@40fd2c7427456e5b/=Sort.40fd2c746a2a3b47
    output:@40fd2c744c862db0/=
    prop:GroupBy=<<EOX
    1
    EOX
    prop:Script=<<EOX
    if firstExec then prevDate = toDate("2033-12-31","CCYY-MM-DD")
    emit * 
    emit End_Date = prevDate
    prevDate = Fd
    EOX
    editor:XY=470,100
    end:Agg
    
    node:Sort
    bretype:core::Sort
    editor:sortkey=5b3253237d914903
    input:@40fd2c743ebf4304/=Convert_to_Date_Type.40fd2c7420761db6
    output:@40fd2c746a2a3b47/=
    prop:CompareOrderExpr=<<EOX
    Fd.reverse()
    EOX
    editor:XY=370,100
    end:Sort
    
    node:Convert_to_Date_Type
    bretype:core::Filter
    editor:Label=Convert to Date Type
    editor:sortkey=5b32535e2f9a4e86
    input:@40fd2c74167f1ca2/=BRD_Data.40fe6c55598828e5
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX
    
    emit *
    emit Fd = date('Price validity from date',"DD-MM-CCYY")
    emit Td = date(ToDate,"DD-MM-CCYY")
    EOX
    editor:XY=270,100
    end:Convert_to_Date_Type
    
    node:BRD_Data
    bretype:core::Static Data
    editor:Label=BRD Data
    editor:sortkey=5b3206a2190e3cab
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    Offering id,Price validity from date,ToDate
    Dummy,04-04-2018,06-04-2018
    Dummy,07-04-2018,11-04-2018
    Dummy,12-04-2018,18-04-2018
    Dummy,19-04-2018,31-12-2030
    Dummy2,02-04-2018,04-04-2018
    Dummy2,05-04-2018,09-04-2018
    Dummy2,10-04-2018,16-04-2018
    Dummy2,17-04-2018,31-12-2030
    
    EOX
    editor:XY=170,100
    end:BRD_Data

  3. #3
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    I'm not completely sure I understood what you need, but I took it that you want the value 11-04-2018 on row 1, 18-04-2018 on row 2, i.e. moving the ToDate values "up" one row. The last in group you mentioned, I took the grouping to be the 'Offering Id'.

    Here's how I saw it:

    Code:
    node:Check_preceding_row
    bretype:core::Lookup
    editor:Label=Check preceding row
    editor:sortkey=5b3250a664ab5e4d
    input:@40fd2c746abc6dc7/=Check_for_last_in_group.4b4668e708143fb4
    input:@40fd2c74486e4494/=Check_for_last_in_group.4b4668e708143fb4
    editor:bend=1=420|110
    editor:bend=1=430|120
    output:@40fd2c7445835585/=
    prop:InputKey=<<EOX
    ('Record_Id'+1).str()
    EOX
    prop:LookupKey=<<EOX
    'Record_Id'.str()
    EOX
    prop:Script=<<EOX
    if matchIsFound then {
    	if '1:LastInGroup' then 
    		_toDate = "2030-12-31".date("CCYY-MM-DD")
    	else
    		_toDate = '2:ToDate'
    }
    else {
    	_toDate = "2030-12-31".date("CCYY-MM-DD")
    }
    
    
    emit 1:*
    emit _toDate as "NewToDate"
    #exclude referencedFields(2,{{^LookupKey^}}) , '1:Record_Id', '1:LastInGroup' 
    
    #where matchIsFound
    
    # Note: If you want to avoid collisions with fields on the inputs
    # prefix the second emit statement with the keyword "default"
    # or "override".
    
    
    EOX
    editor:XY=490,110
    end:Check_preceding_row
    
    node:Check_for_last_in_group
    bretype:core::Agg Ex
    editor:Label=Check for last in group
    editor:sortkey=5b324e844dd22a96
    input:@4b4668c040aa5a85/=BRD_Data.40fe6c55598828e5
    output:@4b4668e708143fb4/=
    prop:GroupBy=<<EOX
    'Offering id'
    EOX
    prop:Script=<<EOX
    
    emit execCount as "Record_Id", *
    override emit 'ToDate'.date("DD-MM-CCYY") as "ToDate"
    emit lastInGroup as "LastInGroup"
    
    
    EOX
    prop:SortInput=false
    editor:XY=360,110
    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:Check_for_last_in_group
    
    node:BRD_Data
    bretype:core::Static Data
    editor:Label=BRD Data
    editor:sortkey=5b3206a2190e3cab
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    Offering id,Price validity from date,ToDate
    Dummy,04-04-2018,06-04-2018
    Dummy,07-04-2018,11-04-2018
    Dummy,12-04-2018,18-04-2018
    Dummy,19-04-2018,31-12-2030
    Dummy2,02-04-2018,04-04-2018
    Dummy2,05-04-2018,09-04-2018
    Dummy2,10-04-2018,16-04-2018
    Dummy2,17-04-2018,31-12-2030
    
    EOX
    editor:XY=220,110
    end:BRD_Data

  4. #4
    Contributor
    Join Date
    Apr 2011
    Location
    Copenhagen
    Posts
    144

    Default

    Thank you both, this made my workday 10% more enjoyable:-)

Posting Permissions

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