Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am developing an application which should show the booked working hours for employees. The interval of these booked hours is a determined period.
For 2011 these are the dates on which the hours booked must be shown:
period 1: 27-12-2010 till 23-01-2011
period 2: 24-01-2011 till 20-02-2011
period 3: 21-02-2011 till 23-03-2011 and so on.
To retrieve the hours from the database in SQL, I used the Posting Date Field in the database. And just put: " where Posting Date between '2011-02-21' and '2011-03-27'. Ofcourse that works. But it's not very usefull to change this for every period applicable.
Is there a way that I can use the period dates above to automatically select the right amount of rows from the database? Instead of changing this by hand every time?
Thanks!
Sorry for my slowness. After considering what the easiest way to solve this is, I would say that a mapping table is quickest to implement. In the attached sample, you can find that the PeriodMap sets the periods on a weekly basis.
I would not recommend adding the month names as you have done in the previous example. Why dont you just modify the MonthName variable in the script if you want to have long month names? I updated my example to also cover this naming need.
I am not an expert on how to creat this in the script so if I would have to make this I would just create a Excel Sheet which contains all the dates of the year(s) and in the second colomn the period name.
If you load this in your Qlikview document you can use.
I would not solve this in the SQL statement, but instead use a master calender table to mark the periods. In the same manner as dates are related to Quarters you would create dates related to your periods.
@ Toni,
Could you give me a headstart on how I do this?
Find the attached simple Master Calendar, with a custom period filed added. In this case each period will be 28 days (vHourBookingPeriodLength) starting from the calender's starting date(vCalendarStart ).
Probably you need to find a way to calculate for a restart of period for each year, as I guess that is the preferred numbering. Still the principle by linking your data dates to a calendar should be the nicest solution for making data linked to time periods.
That is a very good help, thanks!
The thing is, the number of weeks in a period differs. It's 4,4,5 everytime. So period 1 and 2 are 4 weeks, period 3 is 5 weeks.
Any thoughts about that?
That is a bit tricker, but not impossible.
You need to specify the period rule a bit more in detail, meaning what the logic behind it is. For a year with 52 weeks you will have 13 periods, but for years with 53 weeks you will have an extra week. Is the 5 weeks period to handle this extra week? Or is the pattern periodical like 445445445445445 etc? Is the period numbering also cyclic, so that it restarts on 1 after a determined period or is it never ending?
The pattern is periodical:
Period | Number of Weeks | Period Start | Period End |
---|---|---|---|
1 | 4 | 27-12-10 | 23-01-11 |
2 | 4 | 24-01-11 | 20-02-11 |
3 | 5 | 21-02-11 | 27-03-11 |
4 | 4 | 28-03-11 | 24-04-11 |
5 | 4 | 25-04-11 | 22-05-11 |
6 | 5 | 23-05-11 | 26-06-11 |
7 | 4 | 27-06-11 | 24-07-11 |
8 | 4 | 25-07-11 | 21-08-11 |
9 | 5 | 22-08-11 | 25-09-11 |
10 | 4 | 26-09-11 | 23-10-11 |
11 | 4 | 24-10-11 | 20-11-11 |
12 | 5 | 21-11-11 | 25-12-11 |
Also is restarts on 1 every year. For example, these were the periods for 2010.
Period | Number of Weeks | Period Start | Period End |
---|---|---|---|
1 | 4 | 28-12-09 | 24-01-10 |
2 | 4 | 25-01-10 | 21-02-10 |
3 | 5 | 22-02-10 | 28-03-10 |
4 | 4 | 29-03-10 | 25-04-10 |
5 | 4 | 26-04-10 | 23-05-10 |
6 | 5 | 24-05-10 | 27-06-10 |
7 | 4 | 28-06-10 | 25-07-10 |
8 | 4 | 26-07-10 | 22-08-10 |
9 | 5 | 23-08-10 | 26-09-10 |
10 | 4 | 27-09-10 | 24-10-10 |
11 | 4 | 23-10-10 | 21-11-10 |
12 | 5 | 22-11-10 | 26-12-10 |
Because the "rule" is not the same for every year you have to write it down somewhere, or in your script or somewhere else. I just created an Excel file as an example and connected it to a master calander.
Hopelijk heb je er iets aan.
Succes,
Running it via Excel can be a good way to get the system going by applying your intended data model, but in the long run I would definitely generate it automaytically in the script. However due to the logics behind your periods, it likley need to be created in a separate table first and then joined with the calendar. With some FOR loops and variables I am sure this can be accomplished in the script.
As said it is a bit tricky, so no permanent solution to offer right now. I will try to get back with a suggestion later on.
Please mark mine and Dennis entries as helpful in mean time, to show that the thread is going towards a final solution.