filling in missing months

[xariet]

Hi All

Thank you for your help

I have the following input

SchoolID	SchoolName	NumberStudents	NumberTeachers	DateReported	DateSchoolRenamed
1	SantaClaus	50	5	20160501
1	SantaClaus	75	7	20161001
1	SantaClaus	100	7	20161101
1	SantaClaus	120	9	20171101
1	SantaClaus	100	9	20180401
1	SaintNicholas	100	8	20180701	20180701
1	SaintNicholas	110	8	20180801
1	SaintNicholas	120	7	20180901

Basically, we have number of students and teachers reported at the beginning of each month. But if the numbers didn't change then the reported date is missing.

I need to bring in all missing months with the data from previous entry. The desired output is below, I have put in bold all rows which have to be added. I have also attached my workflow, I used too to generate new rows, but not sure why it doesn't work..

SchoolID	SchoolName	NumberStudents	NumberTeachers	DateReported	DateSchoolRenamed
1	SantaClaus	50	5	20160501	20160501
1	SantaClaus	50	5	20160601	20160501
1	SantaClaus	50	5	20160701	20160501
1	SantaClaus	50	5	20160801	20160501
1	SantaClaus	50	5	20160901	20160501
1	SantaClaus	75	7	20161001	20160501
1	SantaClaus	100	7	20161101	20160501
1	SantaClaus	100	7	20161201	20160501
1	SantaClaus	100	7	20170101	20160501
1	SantaClaus	100	7	20170201	20160501
1	SantaClaus	100	7	20170301	20160501
1	SantaClaus	100	7	20170401	20160501
1	SantaClaus	100	7	20170501	20160501
1	SantaClaus	100	7	20170601	20160501
1	SantaClaus	100	7	20170701	20160501
1	SantaClaus	100	7	20170801	20160501
1	SantaClaus	100	7	20170901	20160501
1	SantaClaus	100	7	20171001	20160501
1	SantaClaus	120	9	20171101	20160501
1	SantaClaus	120	9	20171201	20160501
1	SantaClaus	120	9	20180101	20160501
1	SantaClaus	120	9	20180201	20160501
1	SantaClaus	120	9	20180301	20160501
1	SantaClaus	100	9	20180401	20160501
1	SantaClaus	100	9	20180501	20160501
1	SantaClaus	100	9	20180601	20160501
1	SaintNicholas	100	8	20180701	20180701
1	SaintNicholas	110	8	20180801	20180701
1	SaintNicholas	120	7	20180901	20180701
2	KrisKringle	110	6	20181001	20181001

[grazitti_sapna · Accepted solution]

Hi @xariet . Please find below the workflow to achieve your desired output. Let me know if it helped.

[binuacs · Accepted solution]

@xariet One way of doing this

[binuacs]

@xariet Updated your workflow and attached it here.

[xariet]

thank you @binuacs , but I think you have attached my original workflow without changes...

[xariet]

thank you @grazitti_sapna , I used part of your workflow for my solution

[binuacs · Accepted solution]

@xariet sorry for that, attaching the workflow again

[flying008]

Hi,@xariet 

Another way to get your want output, and it fulfill your above post picture. (please see the last row.)

Input						Output
SchoolID	SchoolName	NumberStudents	NumberTeachers	DateReported		SchoolID	SchoolName	NumberStudents	NumberTeachers	DateReported	DateSchoolRenamed
1	SantaClaus	50	5	20160501		1	SantaClaus	50	5	20160501	20160501
1	SantaClaus	75	7	20161001		1	SantaClaus	50	5	20160601	20160501
1	SantaClaus	100	7	20161101		1	SantaClaus	50	5	20160701	20160501
1	SantaClaus	120	9	20171101		1	SantaClaus	50	5	20160801	20160501
1	SantaClaus	100	9	20180401		1	SantaClaus	50	5	20160901	20160501
1	SaintNicholas	100	8	20180701		1	SantaClaus	75	7	20161001	20160501
1	SaintNicholas	110	8	20180801		1	SantaClaus	100	7	20161101	20160501
1	SaintNicholas	120	7	20180901		1	SantaClaus	100	7	20161201	20160501
2	KrisKringle	110	6	20181001		1	SantaClaus	100	7	20170101	20160501
						1	SantaClaus	100	7	20170201	20160501
						1	SantaClaus	100	7	20170301	20160501
						1	SantaClaus	100	7	20170401	20160501
						1	SantaClaus	100	7	20170501	20160501
						1	SantaClaus	100	7	20170601	20160501
						1	SantaClaus	100	7	20170701	20160501
						1	SantaClaus	100	7	20170801	20160501
						1	SantaClaus	100	7	20170901	20160501
						1	SantaClaus	100	7	20171001	20160501
						1	SantaClaus	120	9	20171101	20160501
						1	SantaClaus	120	9	20171201	20160501
						1	SantaClaus	120	9	20180101	20160501
						1	SantaClaus	120	9	20180201	20160501
						1	SantaClaus	120	9	20180301	20160501
						1	SantaClaus	100	9	20180401	20160501
						1	SantaClaus	100	9	20180501	20160501
						1	SantaClaus	100	9	20180601	20160501
						1	SaintNicholas	100	8	20180701	20180701
						1	SaintNicholas	110	8	20180801	20180701
						1	SaintNicholas	120	7	20180901	20180701
						2	KrisKringle	110	6	20181001	20181001