Generate sequence of dates between 2 dates
i have a requirement to “fill in the gaps in time” per se on data from a system. Here is my use case:
A customer signed up with the company for $100 subscription monthly for 3 years. The company ad...
Forum Discussion
There two basic challenges to tackle here.
- Get the “next” date (from the next record) to know how many records to fill
- “Create” records to fill the gap
To get the next date, you need to first combine records within a single JSON document so you can iterate through them as a set. I would think to use a Group By Fields snap to combine the logical sets of records; this will produce an array of records per group.
Now use a Mapper to generate a new field into the group for “next” or “prev” date on each record. Something like this:
$group.map((elem, index, array)=> elem.extend({ "Prev_Effective_Date" : (index==0 ? null : array[index-1].Effective_Date ) } ) )
For the second part, you can use the JSON Generator snap with some Velocity syntax to loop through and create the extra records. It may simplify things if you only output the “base date” and a month incrementor in the JSON Generator, then follow it with a Mapper to calculate the new “Effective_Date” for the gap-fill records.
Sorry I don’t have time to put together an example pipeline for you, but I hope this gives you a potential direction to work towards.
Obviously, this isn’t the only way to do it… I’m sure there are other contributors that have other ideas.
Good luck and happy snapping!
