Discussion Board for collaboration on QlikView Scripting.
I have only year values in field,i do not have any Date field in my table
is it possible to generate Months,Dates for each year???
Create Master Calender
USe below script
Let vMinDate = num(MakeDate(2013,01,01));
Let vMaxDate = num(MakeDate(2015,12,31));
Load Date($(vMinDate)) + RowNo() -1 as Temp_Date
While Date($(vMinDate)) + RowNo() -1 < Date($(vMaxDate));
Load Temp_Date ,
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
Drop table Temp_Cal;
but we do not have max date and min date to creat Master calender so how we can generate ??we can not
Hmm, how would you know which month to use if you have only a year field?
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
try below script
LOAD * Inline [
min(Year) as MinYear
let vMinYear = Peek('MinYear',0,'MaxMinYear');
LOAD *,Year(Date) as Year,
month(Date) as Month,
monthname(Date) as MonthName;
LOAD Date(MakeDate($(vMinYear))+ IterNo()-1,'DD-MM-YYYY') as Date
While MakeDate($(vMinYear))+ IterNo()-1<= Today();
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?
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