topic Re: Create Dates from Year in QlikView

Create Dates from Year

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Create Dates from Year

sunny_talwar — Thu, 29 Jun 2017 16:58:53 GMT

Have you looked at creating a master calendar script?

Re: Create Dates from Year

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:01:07 GMT

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;

Re: Create Dates from Year

MK9885 — Thu, 29 Jun 2017 17:01:36 GMT

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

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:02:16 GMT

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

YearEnd(MakeDate(2017)) as MaxDate

Re: Create Dates from Year

MK9885 — Thu, 29 Jun 2017 17:03:06 GMT

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

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:05:38 GMT

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

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

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

Re: Create Dates from Year

MK9885 — Thu, 29 Jun 2017 17:07:24 GMT

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

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:12:32 GMT

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.

Re: Create Dates from Year

MK9885 — Thu, 29 Jun 2017 17:15:29 GMT

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

Thanks, it is generating all dates now.

Re: Create Dates from Year

Anil_Babu_Samineni — Thu, 29 Jun 2017 17:18:46 GMT

FYI - This won't generate till 31-12-2017. For that you have to use YearEnd() function where i mentioned first post

Re: Create Dates from Year

MK9885 — Thu, 29 Jun 2017 18:45:46 GMT

Ok,

so it will be

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 YearEnd(MakeDate(2017)) as MaxDate,

Now() as MaxDate

AutoGenerate 1;

Is that correct?