We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.
Our Support systems migrated on Saturday, May 21. We'll automatically forward you to the new location for this content.

Calculate end date from next row

Follow
Avatar
Infogix Support
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.
0

Comments

3 comments

Sort by Date Votes
  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    Originally posted by: ThomasT

    Thank you both, this made my workday 10% more enjoyable:-)
    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post