topic Re: Generate date in QlikView

Generate date

Fri, 10 May 2013 08:55:13 GMT

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

Re: Generate date

nilesh_gangurde — Fri, 10 May 2013 09:05:53 GMT

Hiii,

Please see this link.

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

-Nilesh

Re: Generate date

sushil353 — Fri, 10 May 2013 09:07:18 GMT

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

Re: Generate date

sushil353 — Fri, 10 May 2013 09:07:24 GMT

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

Re: Generate date

er_mohit — Fri, 10 May 2013 09:09:52 GMT

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

Re: Generate date

Fri, 10 May 2013 09:18:51 GMT

Hi,

thank you for your answers

I will try to use the calendar

Re: Generate date

Thu, 15 Aug 2013 14:04:42 GMT

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