Splitting of data based on dates

[yoganantha321]

Hi all i have a data set as like shown in the image 1:

Image 1

But I need as split wise based on the month

as shown in image 2:

Image2

Attached excel file for reference.

Regards,

Yoganantha Prakash G P

[Gysbert_Wassenaar · Accepted solution]

Perhaps something like this:

LOAD
    ID,
    Name,
    RangeMax([Start Date],MonthStart([Start Date],IterNo()-1)) as [Start Date],
    RangeMin([End Date], MonthEnd([Start Date],IterNo(),-1)) as [End Date],
    [Lease Type]
FROM
    ...source...
WHILE
    MonthStart([Start Date], IterNo()-1) < [End Date] 
    ;

[yoganantha321]

hi Gysbert,

It is not working.

ID 1 Apple not loaded at all.

[Gysbert_Wassenaar]

The start date of ID 1 lies before its end date:

ID	Name	Start Date	End Date	Leave Type
1	Apple	25/12/2019	06/01/2019	Year end vacation

[yoganantha321]

Thanks Gysbert.