Forum Discussion

mbostrom_dtiq's avatar
mbostrom_dtiq
New Contributor II
5 years ago

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 added services 6 months into the contract which bumped their subscription up to $120 / month, and again a couple months later. but then never again for a couple years. however our ERP only shows the effective dates based on the change. so the json from the system would look this this but I need to create records for every missing month using the previous month if no changes were made. Then when the next change made use that and generate the missing time between that month and subsequent months until the last change. this will allow us to show trending over time and the revenue for that customer by every month in which they have been a customer. what is the best way to achieve this? a parent customer can have multiple locations in which this could change for this example i’ve simplified it to just 1 customer with multiple records so i believe some level of grouping and min/max dates need to be used.

[
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 7,
“Effective_Date”: “2015-12-16T00:00:00.000”,
“Monthly_Revenue”: 100
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 21,
“Effective_Date”: “2016-06-16T00:00:00.000”,
“Monthly_Revenue”: 120
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 44,
“Effective_Date”: “2016-08-16T00:00:00.000”,
“Monthly_Revenue”: 130
},
{
“Parent_Customer_Id”: 12345,
“Location_Site_ID”: 98765,
“Parent_Customer_Name”: “Acme”,
“Business_Name”: “Acme Store 1”,
“Monthly_Change_Tracking_Id”: 55,
“Effective_Date”: “2020-12-16T00:00:00.000”,
“Monthly_Revenue”: 150
}
]

2 Replies

  • koryknick's avatar
    koryknick
    Employee
    5 years ago

    There two basic challenges to tackle here.

    1. Get the “next” date (from the next record) to know how many records to fill
    2. “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!

  • koryknick's avatar
    koryknick
    Employee
    5 years ago

    You could also look into using a Script snap in your choice of languages: Python, Javascript, or Ruby.