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.  Calculate end date from next row

    Employee
    Posted 06-26-2018 02:41

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

    Originally posted by: ThomasT

    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.  RE: Calculate end date from next row

    Employee
    Posted 06-26-2018 08:02

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

    Originally posted by: stonysmith

    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.

    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.  RE: Calculate end date from next row

    Employee
    Posted 06-26-2018 08:14

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

    Originally posted by: gmullin

    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:

    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.  RE: Calculate end date from next row

    Employee
    Posted 07-06-2018 00:52

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

    Originally posted by: ThomasT

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