Calender Script for SQL Database Pull-in

[#1]

Hi,

I've been reading up on the calendar scripts to use in Qlikview but none seem to be working for me. I think the reason has to do with the fact that I'm trying to group the dates I pull-in from my SQL database.

How do I get a calendar script which allows dates to be grouped by month/year to work with a OLEDB database connection?

[sinanozdemir]

Hi Sam,

Are you talking about a master calendar? If then, a master calendar is based on Min and Max dates from facts/dimensions tables? I don't think SQL grouping will have much impact. Although, It is difficult to determine without seeing some sample data.

[#3]

Yeah, I need a master calendar, my problem is that I am unsure how to edit my script to get this to work. E.g.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial . . . .

SQL select * from [*****] where date > '01/01/2013' AND [*****] <> 0;
inner join SELECT * from *******;

This is my query (I've replaced the database names with asterixes as well as a field). Where and in what form would my master calendar go?

[sinanozdemir]

You need to edit the below code to fit into your requirements. I would first create another tab and call it Master Calendar and then place the below code in that tab:

Dates:

LOAD

  Min("Your Date Field") AS MinDate,

  Max("Your Date Field") AS MaxDate

Resident "Your Table Name Which You Want to Pull Dates From";

LET vMinDate = Peek('MinDate', 0, 'Dates');

LET vMaxDate = Peek('MaxDate', 0, 'Dates');

TempCalendar:

LOAD

  Date($(vMinDate) + RowNo() - 1) AS CalDate

  AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

Calendar:

LOAD

  CalDate AS "Your Date Field Which Matches with Other Date Field That You Want to Hook This Up",

  Year(CalDate) AS Year,

  Month(CalDate) AS Month,

  Week(CalDate) AS Week,

  Day(CalDate) AS Day,

  'Q' & Ceil(Month(CalDate) / 3 ) AS Quarter

Resident TempCalendar;

DROP TABLES Dates, TempCalendar;

[Anonymous · Accepted solution]

Hi,

I have attached a calendar for you.

you just need to put

Year(date(SaleDate))&num(Month(date(SaleDate)),'00') as YM,

into the table which has the date in it. (see example qvw)

this calendar allows you to also set a fiscal year. if you do not want to be able to have fiscal year - delete the

Concatenate(Calendar) load  script.

if you have a fiscal year remember that adding multiple calendars duplicates data - so in your frunt end create a list box for calendarType, select a field then in properties tick "Always only 1 selected"

because you have now created a MONTH and YEAR field. you just use them in your front end.

hope this helps.

If you have any problems let me know.

  Gareth

[#6]

When you refer to the table Your Table Name Which You Want to Pull Dates From" how do I construct this? I'm pulling in the dates based on the above SQL Query

[sinanozdemir]

There has to be a date field in your SQL query because you have filter in the WHERE clause (WHERE date > '01/01/2013'.

If the field is called "date", then you can use that field.

Hope this helps.

[#8]

Okay, that's fine. However, is this not the same as "my date field" i.e. both would read CBDate in my case?

[sinanozdemir]

I assumed that you would have two date fields such as "service start date" and "service end date". In this case, you can use something similar to the below script:

As a result of this, you would have dates populated:

Does it come close to what you want to accomplish?