QMC reload job failure Alert mail

• Goto -> QMC-> System -> Setup 
• Select Qlikview Server
• On Right side -> Alerts Tab
• Pass the Email address, in address bar for alert mail.

This will send an alert mail on reload failure on QV document.

Please drop your comments to further discuss this in detail.

Generate your own calendar table in Qlikview?

• We need start date of calendar, in my case I have taken minimum date from fact table.
• We need End Date of calendar, I have taken today's date.

To get min. date from Fact:-

Min:
LOAD min (Date_field) as Min_Date
resident Fact;

let vMin_Date=num(floor(Peek('Min_Date')));
let vMax_Date=num(floor(today()));

To Generate Calendar between min and max date:-

Calendar:
load date(RowNo()+ $(vMin_Date)-1) as Calendar_Date
AutoGenerate ($(vMax_Date)-$(vMin_Date)) ;


This will populate all the dates between min date to max date. Now we can create other date Matrix using Calendar_Date.

Calendar_Table:
load *,
        Month(Calendar_Date) as Month,
  Hour(Calendar_Date) as Hour_Calendar,
  Day(Calendar_Date) as Day,
  Date(floor(Calendar_Date)) as Date,
  Date((floor(Calendar_Date)), 'DD/MM/YYYY') As DateField,
WeekDay(floor(Calendar_Date)) as WeekDay,
Year(date(floor(Calendar_Date))) as Year,
  dual(Month(Calendar_Date)&'-'&Year(Calendar_Date),Year(Calendar_Date)&num(month(Calendar_Date),00)) as YearMonthDis,
  (date(WeekStart(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM') & '-' & date(WeekEnd(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM')) as WeekPeriod,
  MonthStart(floor(Calendar_Date)) as MonthStart,
  Year(Calendar_Date)&num(Month(Calendar_Date),00) as YearMonth,
  Year(Calendar_Date)&num(Week(Calendar_Date),00) as YearWeek,
  'Q'&(FLOOR(NUM(MONTH(Calendar_Date)-1)/3)+1) AS Quarter,
     Date(monthstart(Calendar_Date), 'MMM-YYYY') AS MonthYear,
     Week(Calendar_Date)&'-'&Year(Calendar_Date) AS WeekYear
 
   Resident Calendar;
Drop table Calendar;

Now we have Calendar_Table ready in model to be used.

Date time stamp handling with incremental load using Oracle DB.

• Take Max date from existing QVD file.

maxDate:
load
max(date) as maxDate
FROM
..\1_QVD\your-qvd-file.qvd (qvd);

let vSERVICE_MONITORINGMaxDate = date(peek('maxDate',0,'maxDate'),'DD-MM-YY hh:mm:ss'); // max date parameter

Final_Table:
SQL SELECT *
FROM DB_Table
where to_date(to_char(REQUEST_TIMESTAMP,'DD-MM-RR HH24:MI:SS')) >to_date('$(vSERVICE_MONITORINGMaxDate)','DD-MM-RR HH24:MI:SS');
Concatenate
LOAD *
FROM
[..\1_QVD\your-qvd-file.qvd] (qvd);
store Final_Table into ..\\1_QVD\your-qvd-file..qvd;
drop table Final_Table;
drop table maxDate;


We can also check whether QVD file is empty or not with below Code.

File:
LOAD
QvdNoOfRecords('..\\1_QVD\our-qvd-file.qvd') AS FileNumberOfRecords
autogenerate 1;

let vFileNumberOfRecords = peek('FileNumberOfRecords');


//If file doesn't exist run a full load else run incremental
if '$(vFileNumberOfRecords)' = '' then

//full load
Final_Table:
SQL SELECT *
FROM DB_Table;
store Final_Table into ..\\1_QVD\your-qvd-file.qvd;
drop table Final_Table;
ENFIF