All of the above solutions worked, I was wondering if we could take this one step forwards with date segregation as below
(earlier it was as per end of the year, now its one year cycle, and also the diff between the start and end date should not be greater than 365/366 days considering leap years if possible )
Input :
ID StartDate EndDate
1 2016-05-12 2019-11-30
Output :
ID StartDate EndDate
1 2016-05-12 2016-11-30
1 2016-11-31 2017-11-30
1 2017-11-31 2018-11-30
1 2018-11-31 2019-11-30
this is how the contract management system would also be implemented.
I just tried to build on top of your logic to create the above scenario, was able to do it, refer to the attached pipeline, although design-wise it may not seem that cleaner and concise but it serves the purpose and handles the leap year exception as well
Input:
Output:
It was helpful for contract management problems we have come across so far.
Contract_Date_splitter_2021_10_11.slp (23.2 KB)
A FileReader snap that reads Excel from a URL. A Binary Router which splits the Output as shown below might work
Hi, Can you post an example for this solution.
Thank you
Hi, if this is a google spreadsheet then consider using Spreadsheet Browser (that lists all the spreadsheets along with their worksheets as per the given filter) snap, followed by the Worksheet Reader (that reads a specific given sheet) snap. Supply values from the browser to the reader snap.
And spreadsheet snappack is a premium snappack. FYI.
If you have little bit of flexibility in implementation, you can split that single excel file with multiple sheets into multiple excel files using few lines of code in Python (Note: Assuming schema is same in each sheet) and then you can just use multi file reader to read the excel files.
I ran into this same problem, and found a solution for my scenario. For this, you’ll need to know the names of the sheets that will be in the workbook, or the maximum number of sheets in the workbook. In my example, I knew the names of all the sheets I would need to read.
This process requires a child pipeline since the excel parser can only use sheet names from parameters and not values passed from an upstream document.
My process was:
can we get a sample pipeline of this example?
I was able to use this for parsing multiple sheets but it errors if a sheet does not exist. Does anyone know of a way to ignore the specific error for if a sheet is blank and continue processing?
@Jordan1211 , try error view
What I did was used the error view plus a router that treated the error I was receiving different than all others. ($error.contains(“Sheet with the provided name does not exist:”)). Which basically classified it as an “Informational Error”. This works for now. I am just wondering if there are other options.
