topic Splitting of data based on dates in QlikView

Splitting of data based on dates

yoganantha321 — Sat, 16 Nov 2024 03:52:31 GMT

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

Re: Splitting of data based on dates

Gysbert_Wassenaar — Fri, 12 Apr 2019 09:38:08 GMT

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

Re: Splitting of data based on dates

yoganantha321 — Fri, 12 Apr 2019 12:07:46 GMT

hi Gysbert,

It is not working.

ID 1 Apple not loaded at all.

Re: Splitting of data based on dates

Gysbert_Wassenaar — Fri, 12 Apr 2019 14:21:55 GMT

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

Re: Splitting of data based on dates

yoganantha321 — Tue, 16 Apr 2019 12:25:19 GMT

Thanks Gysbert.