Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Autogenerate date?

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

1 Solution

Accepted Solutions
brenner_martina
Partner - Specialist II
Partner - Specialist II

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**********************************************

View solution in original post

9 Replies
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi Bogdan,

look at the attached file

Greetings from Munich

Martina

Not applicable
Author

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

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**********************************************

Not applicable
Author

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi Bogdan,

yes thats the function. Rename the field

LOAD

Datefield AS Date,

to your datefield.

Not applicable
Author

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

Not applicable
Author

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

Hey Martina,

It finally worked for me, I did as you advised me. You're a genius

Thank you so much!

Bogdan