Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

grajesh_sgp
New Contributor II

can we Generate Months from Year only?

I have only year values in field,i do not have any Date field in my table

year

2013

2014

2015

is it possible to generate Months,Dates for each year???

Tags (1)
8 Replies

Re: can we Generate Months from Year only?

Hi,

Create Master Calender

Regards,

Regards,
Prashant Sangle

Re: can we Generate Months from Year only?

Hi,

USe below script

Let vMinDate = num(MakeDate(2013,01,01));

Let vMaxDate = num(MakeDate(2015,12,31));

Temp_Cal:

Load Date($(vMinDate)) + RowNo() -1 as Temp_Date

AutoGenerate 1

While Date($(vMinDate)) + RowNo() -1 < Date($(vMaxDate));

Master_Cal:

Load Temp_Date ,

date(Temp_Date)as TestDate,

  Day(Temp_Date) as TestDay,

  Month(Temp_Date) as TestMonth,

  Year(Temp_Date) as TestYear,

  Monthname(Temp_Date) as TestMonthYear,

  Week(Temp_Date) as TestWeek,

  Month(Temp_Date) &'-'& Year(Temp_Date) as TestYearMonth

Resident Temp_Cal;

Drop table Temp_Cal;

Regards

Regards,
Prashant Sangle
grajesh_sgp
New Contributor II

Re: can we Generate Months from Year only?

but we do not have max date and min date to creat Master calender so how we can generate ??we can not

MVP
MVP

Re: can we Generate Months from Year only?

Hmm, how would you know which month to use if you have only a year field?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
grajesh_sgp
New Contributor II

Re: can we Generate Months from Year only?

ok this is my requirement I have a QVD with Year field,and i want to extract data always last 12 months including current month for example for august 2015 it should be value from sep 2014 

Re: can we Generate Months from Year only?

try below script

Year:

LOAD * Inline [

Year

2012

2013

2014 ];

MaxMinYear:

LOAD

min(Year) as MinYear

Resident Year;

let vMinYear = Peek('MinYear',0,'MaxMinYear');

Calender:

LOAD *,Year(Date) as Year,

month(Date) as Month,

monthname(Date) as MonthName;

LOAD Date(MakeDate($(vMinYear))+ IterNo()-1,'DD-MM-YYYY') as Date

AutoGenerate(1)

While MakeDate($(vMinYear))+ IterNo()-1<= Today();

Re: can we Generate Months from Year only?

If there is only a year field in the qvd, how will you load only some months within this year?

Can you provide some example data to clarify?

thanks

regards

Marco

mov
Esteemed Contributor III

Re: can we Generate Months from Year only?

If this is the requirement - you don't need QVD with years.  Just create a calendar which starts at the beginning of the month 12 months back (it is your min date), and ends at the end of the current month (it is your max date).

How to create a Calendar

Community Browser