Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
yoganantha321
Contributor II

Splitting of data based on dates

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

table.JPGImage 1

But I need as split wise based on the month

as shown in image 2:

table2.JPGImage2

Attached excel file for reference.

Regards,

Yoganantha Prakash G P

Labels (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Splitting of data based on dates

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
4 Replies
MVP & Luminary
MVP & Luminary

Re: Splitting of data based on dates

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
Contributor II

Re: Splitting of data based on dates

hi Gysbert,

It is not working.

table3.JPG

ID 1 Apple not loaded at all.

MVP & Luminary
MVP & Luminary

Re: Splitting of data based on dates

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
Contributor II

Re: Splitting of data based on dates

Thanks Gysbert.