Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yoganantha321
Creator II
Creator II

Splitting of data based on dates

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

Image 1Image 1

But I need as split wise based on the month

as shown in image 2:

Image2Image2

Attached excel file for reference.

Regards,

Yoganantha Prakash G P

Labels (1)
1 Solution

Accepted Solutions
Gysbert_Wassenaar

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]
;

talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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]
;

talk is cheap, supply exceeds demand
yoganantha321
Creator II
Creator II
Author

hi Gysbert,

It is not working.

table3.JPG

ID 1 Apple not loaded at all.

Gysbert_Wassenaar

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

IDNameStart DateEnd DateLeave Type
1Apple25/12/201906/01/2019Year end vacation

talk is cheap, supply exceeds demand
yoganantha321
Creator II
Creator II
Author

Thanks Gysbert.