Split hours into back dated days based on total hours

[Tanmay_Sarkar]

Hello All,

I’ve stumbled upon a scenario, where I need to split the JSON object into n number of objects (backdated) if the total hours coming in from the source crosses 24, it should split until the point the total hours have been exhausted.

I have added an input JSON array for clarifying my requirement:

[
	{
		"res": "1001",
		"activity": "First",
		"hours": "20",
		"date": "31/01/2022"
	},
	{
		"res": "1001",
		"activity": "Second",
		"hours": "8",
		"date": "31/01/2022"
	}
]

For res 1001 the total hours gathered from both the activities is 28 hours, which is crossing the threshold of 24 hours for a day, what I would like to achieve is to split any one of the object in such a way that the remaining hours (4 hours → 28-24) gets spread across another object for the previous day.

Here is the desired output:

[
	{
		"res": "1001",
		"activity": "First",
		"hours": "20",
		"date": "31/01/2022"
	},
	{
		"res": "1001",
		"activity": "Second",
		"hours": "4",
		"date": "31/01/2022"
	},
	{
		"res": "1001",
		"activity": "Second",
		"hours": "4",
		"date": "30/01/2022"
	}
]

Had the total hours in input be 60 hours then the output would be required to split across 3 days. 24 + 24 + 12, let’s say activity 1 has 24 hours for date 31/01/2022, activity 2 has 24 hours for 31/01/2022 and activity 3 has 12 hours for date 31/01/2022, then activity 2 would have to shift back to 30/01/2022 and activity 3 would have to shift back to 29/01/2022 as activity 2 has occupied 24 hours on 30/01/2022. Hope this makes sense.

Activities will always have the current day’s date (whenever this calculation will occur).

Any help on this would be greatly appreciated, thank you in advance.

Regards,
Tanmay

[koryknick · Accepted solution]

Hi @Tanmay_Sarkar - this is a complex requirement. There are ways to have SnapLogic create multiple documents from a single document, but here you have multiple documents that need to first be analyzed and summarized to determine the total value, then split the “overflow” values into multiple documents. My recommendation is that you look into the Script snap and use the language of your choice (Javascript, Python, Ruby) to do this.

[Tanmay_Sarkar]

Thank you @koryknick

This makes sense. Depending on a script was definitely one of the options (and we managed to get this done with the help of a JavaScript).
Just thought to share this in the community and understand if there’s a way to get this done in SnapLogic directly.

Regards,
Tanmay