Master Calendar for Multiple Table

Mon, 18 Jan 2010 13:38:01 GMT

Dear All,

I need to use one master calendar for multiple date fields from different databases. I read forum topics which are related with this but cannot conclude a solution. Would you please guide me. Here is my code:

Thanks in advance

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 TL;-#.##0,00 TL';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Oca;?ub;Mar;Nis;May;Haz;Tem;A?u;Eyl;Eki;Kas;Ara';
SET DayNames='Pzt;Sal;Çar;Per;Cum;Cmt;Paz';

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=username;Data Source=INTDB;Extended Properties=""] (XPassword is ****);
EXCEL1:
LOAD ID,
 TIP,
 TARIH,
 ACILAN,
 COZULEN,
 ACIK,
 SUREC,
 "SUREC_AD";
SQL SELECT *
FROM MDBADMIN."ZRAPOR_GENEL";
OrderedDatesTable: //Changed
LOAD TARIH RESIDENT EXCEL1 ORDER BY TARIH ASC;
LET varMinDate = Num(Peek('TARIH', 0, 'OrderedDatesTable'));
LET varMaxDate = Num(Peek('TARIH', -1, 'OrderedDatesTable'));
LET vToday = Num(today());
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate as TARIH,
 week(TempDate) AS Week,
 year(TempDate) AS Year,
 month(TempDate) AS Month,
 day(TempDate) AS Day,
 weekday(TempDate) AS WeekDay,
 'Q' & Ceil(Month(TempDate)/3) AS Quarter,
 date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
 week(TempDate)&'-'&Year(TempDate) AS WeekYear,
 Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
 Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate Asc;
DROP TABLE TempCalendar;
DROP TABLE OrderedDatesTable;
CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=TK_REP_USR;Data Source=TK103;Extended Properties=""] (XPassword is ****);
CQ_DB1:
LOAD //DBID,
 ID AS ID_CQ,
 "ISTEKTE_BULUNAN",
 "MISC_INFO",
 KONU,
 STATUS,
 PRIORITY,
 PROJE,
 NAME,
 //"SUBMIT_DATE",
 Date("SUBMIT_DATE") as TARIH,
 ACTUALMANHOUR,
 "GERCEK_ADAMSAAT_ANALIZ",
 PLANNEDMANHOUR,
 "PLANLANAN_ADAMSAAT_ANALIZ",
 YONETICI,
 "COZUM_GRUBU";
SQL SELECT *
FROM "TK_REP_USR"."OZSEV_RAPOR1";
CQ_DB2:
LOAD //DBID,
 ID AS ID_CQ,
 "ACTION_TIMESTAMP",
 "ACTION_NAME",
 "NEW_STATE";
SQL SELECT *
FROM "TK_REP_USR"."OZSEV_RAPOR2_TARIHCE";

Master Calendar for Multiple Table

Tue, 19 Jan 2010 08:04:45 GMT

May be you can use the following code for calender:

CalendarRaw:

load

date(makedate(2008)+recno()-1,'DD-MM-YYYY') as Calendar

autogenerate 365

;

Calendar:

Load

Calendar

day

(Calendar) as Day

month

(Calendar) as Month

year

(Calendar) as Year

weekday

(Calendar) as WeekDay

week

(Calendar) as Week

(weekday(Calendar)='Sun' or HDay_Flag='1','1','0') as

HDay_Flag

topic Master Calendar for Multiple Table in QlikView

Master Calendar for Multiple Table

Master Calendar for Multiple Table