Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table with dates (date_debut, date_fin) and I am trying to load a calendar into QV in order to get a better overview of the ocupied periods in certain months. Would it be possible to generate something that can allow me to select the Year, Month, Day and afterwards match it with the period between the two columns in the table? I read something about auto generate, but couldn't quite get the hang of it. Hope my questions make sense, I'm new at this and I would really appreciate your help.
This is how the table looks like when i load it:
LOAD company_id,
weekends,
periode_id,
projet_id,
user_id,
specialitate_id,
sprojet_id,
sprojet_specialitate_id,
locatie,
date_debut,
date_fin,
duree,
notes
Thank you in advance,
Bogdan
Hi Bogdan,
no problem look here:
//******************* Table Calender *******************
Let varMinDate = Num(Makedate(2008,1,1));
Let varMaxDate = Num(Makedate(Year(today())+1,12,31));
Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Calender:
LOAD
Datefield AS Date,
Year(Datefield) AS Year,
Month(Datefield) as Month,
Dual(Date((Datefield),'MMMM'),Num(Month(Datefield))) as Months,
Floor(Monthstart(Datefield)) as Monthstart,
Week(Datefield) AS Week,
Weekday(Datefield) AS Weekday,
Day(Datefield) AS Day,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter,
Monthname(Datefield) AS RollMonth,
Num(Monthname(Datefield)) AS NumRollmontj,
(Num(Floor(Monthend(Datefield)))-Num(Floor(Monthstart(Datefield)))+1) as NumberDays,
If(Num(Weekday(Datefield))<5,1,0) as WorkingDay,
YearToDate(Datefield,0) as YTDActualYear,
YearToDate(Datefield,-1) as YTD1PreYear,
YearToDate(Datefield,-2) as YTD2PreYear,
YearToDate(Datefield,1) as YTDFollowingYear
RESIDENT Datefield;
//Delete temp table
DROP TABLE Datefield;
//Clean Up Variables
SET varMinDate = ;
SET varMaxDate = ;
//*************************************Ende Script**********************************************
Hi Bogdan,
look at the attached file
Greetings from Munich
Martina
Hello Martina,
Thank you for the quik reply. Unfortunately I'm currently using the Personal Edition of QV, so I cannot open that file. Would it be possible for you to paste it in a commnt?
Thank you so much,
Bogdan
Hi Bogdan,
no problem look here:
//******************* Table Calender *******************
Let varMinDate = Num(Makedate(2008,1,1));
Let varMaxDate = Num(Makedate(Year(today())+1,12,31));
Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Calender:
LOAD
Datefield AS Date,
Year(Datefield) AS Year,
Month(Datefield) as Month,
Dual(Date((Datefield),'MMMM'),Num(Month(Datefield))) as Months,
Floor(Monthstart(Datefield)) as Monthstart,
Week(Datefield) AS Week,
Weekday(Datefield) AS Weekday,
Day(Datefield) AS Day,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter,
Monthname(Datefield) AS RollMonth,
Num(Monthname(Datefield)) AS NumRollmontj,
(Num(Floor(Monthend(Datefield)))-Num(Floor(Monthstart(Datefield)))+1) as NumberDays,
If(Num(Weekday(Datefield))<5,1,0) as WorkingDay,
YearToDate(Datefield,0) as YTDActualYear,
YearToDate(Datefield,-1) as YTD1PreYear,
YearToDate(Datefield,-2) as YTD2PreYear,
YearToDate(Datefield,1) as YTDFollowingYear
RESIDENT Datefield;
//Delete temp table
DROP TABLE Datefield;
//Clean Up Variables
SET varMinDate = ;
SET varMaxDate = ;
//*************************************Ende Script**********************************************
Thank you very much for this Martina. Would I be able to link this to the dates I have in the original table, so for example when I select "May" the only values shown are the dates from May from the original table? I don't know if it helps, but the date format from the table looks like this: DD-MM-YY.
Thank you,
Bogdan
Hi Bogdan,
yes thats the function. Rename the field
LOAD
Datefield AS Date,
to your datefield.
Hello Martina,
I think i got it to work. I'll need to check and let you know when I'm sure
Thank you so much, you are a life saver.
In your debt,
Bogdan
Hello Martina,
First of all thank you for your patience. Still could not get it to work, i cannot link the original table to the auto generated dates. When you told me to rename the field datefield to my datefield, do you mean replace where it says 'datefield' in the script with 'date_debu't, the first date I have in the original table?
Thanks again,
Bogdan
Hi Bogdan,
Pardon, that was a little bit struggly: I mean change the wod date in my script to your fieldname of the date:
LOAD
Datefield AS Date,
Your script:
LOAD
Datefield AS date_debut
...
if you need a calendar also for the datefield date_fin, you can enter a new calendar, but then rename all fields.
Greetings from Munich
Martina
Hey Martina,
It finally worked for me, I did as you advised me. You're a genius
Thank you so much!
Bogdan