topic How to generate weeks between two dates? in QlikView

How to generate weeks between two dates?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to generate weeks between two dates?

Anil_Babu_Samineni — Wed, 06 Dec 2017 12:58:49 GMT

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;

Re: How to generate weeks between two dates?

avkeep01 — Wed, 06 Dec 2017 13:50:41 GMT

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;

Re: How to generate weeks between two dates?

Anonymous — Wed, 06 Dec 2017 14:15:07 GMT

Thanks Anil,

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

Re: How to generate weeks between two dates?

Anil_Babu_Samineni — Wed, 06 Dec 2017 14:19:01 GMT

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;

Re: How to generate weeks between two dates?

its_anandrjs — Wed, 06 Dec 2017 14:24:08 GMT

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;

Re: How to generate weeks between two dates?

Anonymous — Wed, 06 Dec 2017 14:35:11 GMT

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.

Re: How to generate weeks between two dates?

Anonymous — Wed, 06 Dec 2017 14:41:47 GMT

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

Re: How to generate weeks between two dates?

its_anandrjs — Wed, 06 Dec 2017 14:45:14 GMT

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

Re: How to generate weeks between two dates?

Anil_Babu_Samineni — Wed, 06 Dec 2017 15:45:06 GMT

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

Re: How to generate weeks between two dates?

jhstrange — Tue, 23 Oct 2018 21:39:28 GMT

Hi Anand,I tried the script but the output log shows "Field 'Date' not found".

Can you take a look once?