Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
MK9885
Honored Contributor II

Create Dates from Year

Hello,

I  would like to create dates out of Year.

I do not have Date field, no Month field but just Year Field.

I used MakeDate function and it just generate 4 dates for me

I've years from 2014 to 2017

and I want dates for each month for these years....

01/01/2014 till 12/31/2017

neelamsaroha1575

stalwar1

Thanks.

1 Solution

Accepted Solutions

Re: Create Dates from Year

You may mis understanding me, That's why you pasted entire script

Any how, That is what i meant to say if you want all years and links you can use some thing like below

tabCalendar: 

LOAD *, 

     Week(Date) as Week, 

     Month(Date) as Month,

     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

     Year(Date) as Year   

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD MakeDate(2014) as MinDate, 

     Now() as MaxDate

AutoGenerate 1;


This will return from Jan 01 2014 to 29 jun 2017. And it will split into month, year, week, quarter as well. I am sorry, if i mi read your question.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

11 Replies

Re: Create Dates from Year

Have you looked at creating a master calendar script?

Re: Create Dates from Year

Can you try this?

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     MakeDate(2017) as MaxDate
AutoGenerate 1;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Honored Contributor II

Re: Create Dates from Year

I just tried and created dates out of it.

The Year field I have it as Dimension in one of the tables

And I created Master Calendar and just renamed the field in my Master to match the name of the Year field in my Dim.

This Dim is Static file and the values will not change....

So will that work?

Cus If I select Year from Master, the Years in my Dim is also selected accordingly as needed.

Re: Create Dates from Year

I assume MakeDate(2017) create only from Jan 01. Then you can use like

YearEnd(MakeDate(2017)) as MaxDate

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Honored Contributor II

Re: Create Dates from Year

I just tried and when I select year, it is selecting only 01/01/YYYY for every year but not giving all months.

Re: Create Dates from Year

First run this code and show the images how you are getting for min and max dates

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     MakeDate(2017) as MaxDate
AutoGenerate 1


Or


LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     YearEnd(MakeDate(2017)) as MaxDate
AutoGenerate 1

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Honored Contributor II

Re: Create Dates from Year

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap: 

    MAPPING LOAD  

    rowno() as Month, 

    'Q' & Ceil (rowno()/3)  as Quarter 

   

    AUTOGENERATE (12); 

     

       varMinDate = num(date(mid('2013-01-01',1,10 ),'YYYY-MM-DD'));

//       varMaxDate = num(date(mid('2015-12-31',1,10 ),'YYYY-MM-DD'));

      //varMaxDate = num(date(mid('2017-12-01',1,10 ),'YYYY-MM-DD'));

      

      

       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

     

TempCalendar: 

    LOAD 

                   $(varMinDate) + Iterno()-1 as Num, 

                   Date($(varMinDate) + IterNo() - 1) as TempDate 

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

  

//Date Dimension

MasterCalendar: 

Load 

               

trim(date(TempDate,'YYYYMMDD')) as [DateID],

date(TempDate,'MM/DD/YYYY') as [Effective Date],

    day(TempDate) as Day,

year(TempDate) as Years,

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

  Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,                  

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

    weekDay(TempDate) as [Week Day]

         

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

Drop Table  QuarterNAME;

I used this and it's generating dates and I linked my Master Calendar to My dimension with Year Key

Re: Create Dates from Year

You may mis understanding me, That's why you pasted entire script

Any how, That is what i meant to say if you want all years and links you can use some thing like below

tabCalendar: 

LOAD *, 

     Week(Date) as Week, 

     Month(Date) as Month,

     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

     Year(Date) as Year   

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD MakeDate(2014) as MinDate, 

     Now() as MaxDate

AutoGenerate 1;


This will return from Jan 01 2014 to 29 jun 2017. And it will split into month, year, week, quarter as well. I am sorry, if i mi read your question.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

MK9885
Honored Contributor II

Re: Create Dates from Year

Yes, this is much simpler and smaller code compared to mine.

Thanks, it is generating all dates now.