Generate date

[#1]

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

[nilesh_gangurde]

Hiii,

Please see this link.

http://community.qlik.com/thread/48693

-Nilesh

[sushil353]

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

[sushil353]

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

[er_mohit]

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

[#6]

Hi,

thank you for your answers

I will try to use the calendar

[#7]

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