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: 
Anonymous
Not applicable

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
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
avkeep01
Partner - Specialist
Partner - Specialist

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;

Anonymous
Not applicable
Author

Thanks Anil,

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

Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
its_anandrjs

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;

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

its_anandrjs

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)