Comparing Two Consecutive Subobjects

[pgary]

I’m trying to move job history records from one system into another using SnapLogic, and the actual data transformation is working great. However, a requirement has come up that I need to check that, in the case of workers who were terminated and later rehired, they can’t have any activity in between the termination and rehire. Sometimes the data gets dirty though, and they had things like job changes or compensation changes while a worker was technically terminated listed in the system.

I’ve built the extraction and grouped each set of data, but I can’t figure out how to compare the two consecutive elements so I can use it to route. So for instance:

{
“groupBy”: {
“Employee ID”: “21194”
},
“Employee”: [
{
“Employee ID”: “21194”,
“Effective Date”: “1/15/2009”,
“Action”: “Hire”
},
{
“Employee ID”: “21194”,
“Effective Date”: “10/29/2009”,
“Action”: “Termination”
},
{
“Employee ID”: “21194”,
“Effective Date”: “10/29/2010”,
“Action”: “Rehire”
},
{
“Employee ID”: “21194”,
“Effective Date”: “09/14/1994”,
“Action”: “Compensation Change”
},
]
}

I want to find the node where the action is “Termiation”, then look at the next node and see if the action is “Rehire”, or if there is no other node after “Termination” If so, that is a valid record. If not, that needs to go to a different queue for error reporting.

I’ve tried every way I can think of, and am still stuck. Can anyone help?

[Michael]

Hi, I’m not a person knowledgeable enough to give you a solution, but I was curious when I read this. It looks like a logic problem. If after termination and there’s a comp or job change action, wouldn’t it always get routed to error handling (since the dirty data would occur before a rehire action, according to how you described it)?

[pgary]

What happens is that people are scheduled to be rehired. Their compensation is updated. Then their rehire date changes, but that compensation update isn’t changed to match the rehire date. So you have some messy data laying around.

Now we’re moving to a new system, and that system won’t allow the dirty data to be loaded. Thus I need to catch what is causing problems before we try and load anything.

[ptaylor]

Try this pipeline:

Community7373_2020_04_29 (1).slp (6.2 KB)

The crux is a Mapper with an expression like this:

match $.Employee.findIndex(e => e.Action == "Termination") {  
 i if i == -1 => "VALID",
 i if i == $.Employee.length - 1 => "VALID",
 i if $.Employee[i+1].Action == "Rehire" => "VALID",
 _ => "INVALID"
}