Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Valued Contributor II

Re: Autogenerate date?

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

9 Replies
brenner_martina
Valued Contributor II

Re: Autogenerate date?

Hi Bogdan,

look at the attached file

Greetings from Munich

Martina

Highlighted
Not applicable

Re: Autogenerate date?

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
Valued Contributor II

Re: Autogenerate date?

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

Re: Autogenerate date?

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
Valued Contributor II

Re: Autogenerate date?

Hi Bogdan,

yes thats the function. Rename the field

LOAD

Datefield AS Date,

to your datefield.

Not applicable

Re: Autogenerate date?

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

Re: Autogenerate date?

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
Valued Contributor II

Re: Autogenerate date?

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

Re: Autogenerate date?

Hey Martina,

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

Thank you so much!

Bogdan