Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
laleh-haidari
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
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;

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;

laleh-haidari
Contributor III
Contributor III
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;

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;

laleh-haidari
Contributor III
Contributor III
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.

laleh-haidari
Contributor III
Contributor III
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?

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)