Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm begeener on Qlikview and I have a problem with the dates.
In fact I have Excel file sourced with dates, in this file I have inputs and outputs of staff.
But if nobody has left the compagny or arrived in the compagny in february (for exemple) I don't have "february" in my dimension "Month".
So I would like to generate a table with the months,the quarter and the year columns.
And then map my table generated with my data from excel file.
I think it should be possible,
Thank you alot in advance for your help
hi,
You can use master calender for this issue.. below is the sample script.
MainTable:
LOAD
Period,
FROM
[..\Data files\QVD's\Flight Data.qvd]
(qvd);
temp_calender_range:
LOAD
num(Date#(min(Period),'YYYYMM')) as MinDate,
num(Date#(max(Period),'YYYYMM')) as MaxDate
Resident MainTable;
let vMinDate= peek('MinDate',0,'temp_calender_range');
let vMaxDate= peek('MaxDate',0,'temp_calender_range');
DROP Table temp_calender_range;
[Master Calender]:
LOAD Distinct
Year(Temp_Date) *100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date,'YYYY-MM') as [Year-Month],
'Q' & Ceil(Month(Temp_Date)/3) as [Quarter];
//////Perceding Load//////
LOAD Distinct
MonthStart($(vMinDate)+IterNo()-1) as Temp_Date
AutoGenerate(1)
While $(vMinDate)+IterNo()-1 <= $(vMaxDate);
let vMinDate= Null();
let vMaxDate= Null();
In the above script instead of period you can take your date field.
HTH
Sushil
hi,
You can use master calender for this issue.. below is the sample script.
MainTable:
LOAD
Period,
FROM
[..\Data files\QVD's\Flight Data.qvd]
(qvd);
temp_calender_range:
LOAD
num(Date#(min(Period),'YYYYMM')) as MinDate,
num(Date#(max(Period),'YYYYMM')) as MaxDate
Resident MainTable;
let vMinDate= peek('MinDate',0,'temp_calender_range');
let vMaxDate= peek('MaxDate',0,'temp_calender_range');
DROP Table temp_calender_range;
[Master Calender]:
LOAD Distinct
Year(Temp_Date) *100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date,'YYYY-MM') as [Year-Month],
'Q' & Ceil(Month(Temp_Date)/3) as [Quarter];
//////Perceding Load//////
LOAD Distinct
MonthStart($(vMinDate)+IterNo()-1) as Temp_Date
AutoGenerate(1)
While $(vMinDate)+IterNo()-1 <= $(vMaxDate);
let vMinDate= Null();
let vMaxDate= Null();
In the above script instead of period you can take your date field.
HTH
Sushil
hiii
for master calendar generates try this code after that join this table with your excel datA
make sure in both table date format is similar
// Calender
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(MakeDate(2030,12,31));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
num(WeekDay(Today()))as ppp,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
'W' & Ceil(Week(TempDate)/8) as Fiscalweek,
Year(TempDate) AS CalendarYear,
Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3), Year(TempDate) & Ceil(Month(TempDate)/3)) as YearQtr,
'FY' & Right( Year(TempDate),2) as YearQtrfy,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
YearName(TempDate,0,2)as FiscalYear
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
hope it helps
Hi,
thank you for your answers
I will try to use the calendar
Hi sushil,
Is there posiblity to attach the Flight Data.qvd .if that so could you please do that for me.I'm looking for that qvd .
Thanks