topic Re: Calender Script for SQL Database Pull-in in QlikView

Calender Script for SQL Database Pull-in

Mon, 10 Aug 2015 16:18:49 GMT

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?

Re: Calender Script for SQL Database Pull-in

sinanozdemir — Mon, 10 Aug 2015 16:50:56 GMT

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.

Re: Calender Script for SQL Database Pull-in

Mon, 10 Aug 2015 16:55:56 GMT

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?

Re: Calender Script for SQL Database Pull-in

sinanozdemir — Mon, 10 Aug 2015 17:11:56 GMT

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;

Re: Calender Script for SQL Database Pull-in

Anonymous — Mon, 10 Aug 2015 17:15:44 GMT

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

Re: Calender Script for SQL Database Pull-in

Mon, 10 Aug 2015 18:17:21 GMT

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

Re: Calender Script for SQL Database Pull-in

sinanozdemir — Mon, 10 Aug 2015 18:20:57 GMT

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.

Re: Calender Script for SQL Database Pull-in

Mon, 10 Aug 2015 18:28:37 GMT

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

Re: Calender Script for SQL Database Pull-in

sinanozdemir — Mon, 10 Aug 2015 19:01:23 GMT

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?