topic Re: Generate the fortnight (two weeks) time intervals for a given date range in QlikView

Generate the fortnight (two weeks) time intervals for a given date range

andymanu — Tue, 05 Feb 2019 05:43:21 GMT

Hi All,

I need to generate the two weeks time periods for a given date range.

For an example, assume the date range is 25/06/2018 - 5/08/2018, the resulted output should be;

25/06/2018 - 8/07/2018

9/07/2018 - 22/07/2018

23/07/2018 - 5/08/2018.

I found the below code to generate the in between dates which is working fine.

"Vacation Start Date" + IterNo() -1 as "Vacation Date"
Resident Vacation_Dates_Table
WHILE "Vacation Start Date" + IterNo() -1 <= "Vacation End Date";

Need a similar one to generate the above said intervals and need to group them (ex - 25/06/2018 - 8/07/2018) as shown above.

Appreciate you valuable feedback in advance.

Kind regards,

Andy

Re: Generate the fortnight (two weeks) time intervals for a given date range

HirisH_V7 — Tue, 05 Feb 2019 06:42:45 GMT

Hi Check the below code for generating the weekstart and enddates for the required dates.

Date:
LOAD * INLINE [
    DateFrom, DateTo
    25/06/2018, 5/08/2018
];

Let vMin=Num(Date#(Peek('DateFrom'),'DD/MM/YYYY'));
Let vMax=Num(Date#(Peek('DateTo'),'DD/MM/YYYY'));

Drop table Date;

Temp:
Load 
Date($(vMin) + RowNo() - 1) AS Vacation_Date
AutoGenerate (1)
WHILE $(vMin) + IterNo() -1 <= $(vMax);

Data:
Load *,
WeekStart(Vacation_Date) & 'To ' & WeekEnd(Vacation_Date)  as  Week_Range

Resident Temp;

DROP table Temp;

Do the mods required for your requirement accordingly. Let me know if any.

PFA QVW.

HTH,

Hirish

Re: Generate the fortnight (two weeks) time intervals for a given date range

andymanu — Tue, 05 Feb 2019 23:07:41 GMT

Hi Hirish,

Thanks for the valuable reply.

FirstWeekDay=6

However, after changing the default setting to 7 (instead of 6), I was able to get the expected results.

1. Is there any other way to overcome the above said without changing the default main page setting?

2. In addition to above, I want to group the weeks in to pairs (cos generally, the salaries are paid fortnightly and it is based on the hours worked for the respective fortnight).

Could you please help me with grouping the individual Week_Range in to pairs and the final result for the above provided date range should be,

25/06/2018 To 8/07/2018

9/07/2018 To 22/07/2018

23/07/2018 To 5/08/2018

Thank you in advance.

Kind regards,

Andy

It is working but had to change one of the default setting of the "Main" page . After changing the "SET FirstWeekDay=7;"

only it gave the correct results. Is there any other way I could

Re: Generate the fortnight (two weeks) time intervals for a given date range

andymanu — Tue, 05 Feb 2019 23:09:35 GMT

Please disregard the last few lines starting with " It is working but ...." cos I have forgotten to delete that bit

Re: Generate the fortnight (two weeks) time intervals for a given date range

HirisH_V7 — Wed, 06 Feb 2019 07:34:37 GMT

Please Find attached updated QVW.

Pl Check below code, hope it answers all your queries.

Date:
LOAD * INLINE [
    DateFrom, DateTo
    25/06/2018, 5/08/2018
];

Let vMin=Num(Date#(Peek('DateFrom'),'DD/MM/YYYY'));
Let vMax=Num(Date#(Peek('DateTo'),'DD/MM/YYYY'));

Drop table Date;

Temp:
Load 
Date($(vMin) + RowNo() - 1) AS Vacation_Date
AutoGenerate (1)
WHILE $(vMin) + IterNo() -1 <= $(vMax);

Data:
Load *,
If(Mod(Week(Vacation_Date,0,7),2)=0,
WeekStart(Vacation_Date,0,7) & ' To ' & WeekEnd(Vacation_Date,1,7),
WeekStart(Vacation_Date,-1,7) & ' To ' & WeekEnd(Vacation_Date,0,7)) as Week_Range


Resident Temp;

DROP table Temp;

HTH,

Hirish

Re: Generate the fortnight (two weeks) time intervals for a given date range

andymanu — Wed, 06 Feb 2019 21:49:30 GMT

HI Hirish,
Thank you very much Hirish.
Yes, its working now as I expected. Thanks a lot, learned something new about grouping weeks also.
Really great.......
Your answer was clear and prompt.
Kind regards,
Andy

Re: Generate the fortnight (two weeks) time intervals for a given date range

HirisH_V7 — Thu, 07 Feb 2019 05:29:18 GMT

No problem!
Enjoy Qliking.. 🙂