topic Re: Calendar with Indicators (Holiday, Weekend, Month-end) in QlikView

Calendar with Indicators (Holiday, Weekend, Month-end)

Anonymous — Tue, 24 Mar 2015 23:04:00 GMT

Is there a way to generate the following indicators in QlikView:

Weekday Indicator - to indicate whether a date is a week day or a week end
MonthEnd Indicator - to indicate whether a date occurs during month end or not
Holiday Indicator - to indicate whether a date falls on a public holiday or not

i have been able to generate such a calendar in Microsoft SQL Server, can this be incorporated into QlikView in order to work with a QlikView generated calendar?

Regards

Chris

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

mrossoit — Tue, 24 Mar 2015 23:35:16 GMT

1. use weekday(date) to find what day of the week (from 0 to 6) is your date. Values 5 and 6 coul be your week end

2. if date = monthend(makedate(year(date), month(date), day(date))) then your date occurs during month end

3. if your SQL Server calendar is generated by a single instruction (one query or more in union) you could load it into your qvw and then find if your date is a public holiday using a qlikview's matching function

Hope it helps

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

Anonymous — Tue, 24 Mar 2015 23:51:01 GMT

Many thanks for your response. Kindly elaborate on your responses, i am new to QlikView. I have created a key called Period in my QlikView Calendar which is Year(Date)*100 + Month(Date). I have also created such a key in my SQL server auxiliary calendar to join the QlikView calendar when i import the SQL Sever calendar. However when i try to use the SQL Server calendar to filter my application with the indicators in the SQL Server, no records appear at all. What could be going wrong?

Regards

Chris

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

mrossoit — Wed, 25 Mar 2015 00:03:17 GMT

It's hard to say what could be the problem blindly.

Post your script or your qvw, please.

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

Anonymous — Wed, 25 Mar 2015 00:26:09 GMT

Many thanks, the QlikView app is too big to post, herewith the scripts:

The auxiliary calendar pulled from MSQL as follows:

OLEDB CONNECT32 TO [Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;Initial Catalog=PharmaMarketAudit;Data Source=localhost\DATAWAREHOUSE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=STRATDIGM-2;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE];

[Auxiliary Calendar]:
LOAD //DateKey,
Period,
     HolidayDescription,
     HolidayIndicator,
     MonthEndIndicator,
     WeekdayHolidayIndicator,
     WeekdayIndicator;
SQL SELECT Period,
     HolidayDescription,
     HolidayIndicator,
     MonthEndIndicator,
     WeekdayHolidayIndicator,
     WeekdayIndicator
FROM PharmaMarketAudit.dbo.Calendar;

The QlikView master calendar is as follows:

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([FullDate]))) as minDate,
max(Date(Floor([FullDate]))) as maxDate
Resident [Dates];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID]
;
LOAD DISTINCT
Date(Floor(TempDate)) as [FullDate],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

The other script is as follows:

OLEDB CONNECT32 TO [Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;Initial Catalog=TK463DW;Data Source=localhost\DATAWAREHOUSE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=STRATDIGM-2;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE];

//-------- Start Multiple Select Statements ------
LOAD City,
     CountryRegion,
     CustomerDwKey,
     EmailAddress,
     FullName,
     Gender,
     MaritalStatus,
     Occupation,
     StateProvince;
SQL SELECT City,
     CountryRegion,
     CustomerDwKey,
     EmailAddress,
     FullName,
     Gender,
     MaritalStatus,
     Occupation,
     StateProvince
FROM TK463DW.dbo.Customers;

LOAD DateKey & '|' & ProductKey as ProductDateKey,
CustomerDwKey,
     DateKey,
     DiscountAmount,
     OrderQuantity,
     ProductKey,
     PromotionKey,
     SalesAmount,
     UnitPrice;
SQL SELECT CustomerDwKey,
     DateKey,
     DiscountAmount,
     OrderQuantity,
     ProductKey,
     PromotionKey,
     SalesAmount,
     UnitPrice
FROM TK463DW.dbo.InternetSales;

LOAD CategoryName,
     Color,
     ProductKey,
     ProductName,
     Size,
     SubcategoryName;
SQL SELECT CategoryName,
     Color,
     ProductKey,
     ProductName,
     Size,
     SubcategoryName
FROM TK463DW.dbo.Products;

LOAD DiscountPct,
     EndDate,
     EnglishPromotionCategory,
     EnglishPromotionName,
     EnglishPromotionType,
     MaxQty,
     MinQty,
     PromotionKey,
     StartDate;
SQL SELECT DiscountPct,
     EndDate,
     EnglishPromotionCategory,
     EnglishPromotionName,
     EnglishPromotionType,
     MaxQty,
     MinQty,
     PromotionKey,
     StartDate
FROM TK463DW.dbo.Promotion;

[Dates]:
LOAD CalendarQuarter,
     CalendarYear,
     DateKey,
     FullDate,
     MonthNumberName;
SQL SELECT CalendarQuarter,
     CalendarYear,
     DateKey,
     FullDate,
     MonthNumberName
FROM TK463DW.dbo.Dates;

LOAD DateKey & '|' & ProductKey as ProductDateKey,
//DateKey,
    MovementDate,
     //ProductKey,
    UnitCost,
     UnitsBalance,
     UnitsIn,
     UnitsOut;
SQL SELECT DateKey,
     MovementDate,
     ProductKey,
     UnitCost,
     UnitsBalance,
     UnitsIn,
     UnitsOut
FROM TK463DW.dbo.ProductInventory;

//-------- End Multiple Select Statements ------

Also attached is the QlikView table overview

Regards

Chris

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

mrossoit — Wed, 25 Mar 2015 01:02:16 GMT

I'm sorry, I'll read it better tomorrow on my computer. I'm loosing my eyes reading your code on my iphone's screen.

Have you checked if your tables are correctly linked through Period? Try to add all their fields to a simple table object.

It's not clear to me why you linked your calendars through period (YYYYMM) and you haven't put them in join using also the day of you date and the day of the holiday. Doing it in this way, when two values match, a new flag setted to true would say you if that date is a holyday

Sorry for my English

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

mrossoit — Wed, 25 Mar 2015 09:10:11 GMT

Reading your 3rd question you need to find if a date (a full date) falls on a holiday listed in your auxiliary calendar so you have to load full holiday dates from SQL Server.

Try to add field FullDate in your Auxiliary Calendar.

Pay attention to date format if you want that fields named FullDate, located in both calendars, correctly match.

I hope that I have correctly understood your question.

Regards

Re: Calendar with Indicators (Holiday, Weekend, Month-end)

Anonymous — Wed, 25 Mar 2015 13:54:09 GMT

Massimiliano

Many thanks for your help, i managed to see my problem with the MS SQL calendar. The dates generated in the calendar did not cover the periods in the fact tables that is why i was getting no results in my selections.

Once again thank you very much for your input

Regards

Chris