Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to generate weeks between two dates?

Hi community,

If I have startDate and endDate in my data, how can I create weeks starting from the startDate field and not the start of the year. So, basically I want to populate weeks between the two dates.

Thanks

10 Replies
Highlighted

May be this?

Load *, Week(Date) as Week;

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD Min(startDate) as MinDate, 

     Max(endDate) as MaxDate

AutoGenerate 1;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Partner
Partner

Hi Laleh,

I would suggest the method from Anil Babu. If you want another method you could use a Cartesion join:

Try

ExampleTable:

LOAD

[StartDate],

[EndDate]

FROM [source];

LEFT JOIN (ExampleTable) LOAD

Date

RESIDENT [Master Calendar];

NOCONCATENATE LOAD

[StartDate],

[EndDate],

Date,

WEEK(Date) AS Week

RESIDENT ExampleTable

WHERE DATE(Date ) >= DATE([StartDate]) AND DATE(Date) <= DATE([EndDate])

;

DROP TABLE ExampleTable;

Highlighted
Contributor III
Contributor III

Thanks Anil,

Just wondering if I have to use resident load instead of autogenerate?

Highlighted

May be this?

Sample:

Load * From Table;

CalendarMaster:

LOAD Week(Final_Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Final_Date While Date(MinDate + IterNo() - 1) <= MaxDate;

Load

    Min(startDate) AS MinDate,

    Max(endDate) AS MaxDate

RESIDENT Sample;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

By resident load you can try this way

let startDate  = Num(MakeDate(2015,1,1));

let endDate  = Num(MakeDate(2017,12,30));

tmpCalendar:

Load

Date($(startDate) + IterNo() -1) as Date

AutoGenerate 1

While Date($(startDate) + IterNo() -1) <= $(endDate);

Calendar:

LOAD

*,

Week(Date) as Week

Resident tmpCalendar;

DROP Table tmpCalendar;

Highlighted
Contributor III
Contributor III

Anil,

Thanks for this, however my challenge is to create week intervals based off of a period of 84 days. So, essentially my data will only have week 1 to week 12 given the startDate and endDate.

Highlighted
Contributor III
Contributor III

is it possible to replace the hard coded dates in the variables with my start and end date columns?

Highlighted

Yes it is possible but keep the name same and check the variable values also will it return [properly or not.

Highlighted

Okay, Can you make sample and explain exact result set you want to see?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)