topic Master Calendar Joining Mutliple Date Fields in QlikView

Master Calendar Joining Mutliple Date Fields

Wed, 13 Jan 2010 23:43:44 GMT

I have found numerous posts discussing this situation I am having and have heard of using Set Analysis to solve this issue. None of this seems to work for me or I am missing how to do this.

I would like to create a master calendar able to connect one or more data table fields together. For example the query I have below contains two different date fields. When I link (LOAD DISTINCT order_date...) to the master calendar my date for the requested_date becomes skewed. It is reducing on order_date, I want just a generic date to be created. I also tried creating a calendar independent of my data, but I do not know how to link it back up to the master calendar since it would have no correlation to my query.

I figure their is an easier way to link these two date fields together, I am just missing it. I would like some suggestions or ideas on how to do this. I would prefer to have one master calendar application for all my QV applications. I would like the linkage to be an outer join not an inner join. Show me all the dates even if 1 of the 3 fields did not have an entry that day.

Master Calendar Joining Mutliple Date Fields

Thu, 14 Jan 2010 22:07:28 GMT

deleted

Master Calendar Joining Mutliple Date Fields

Thu, 14 Jan 2010 22:07:35 GMT

After enough messing around. I wound back at the start. Below is the master calendar I ended up using.

Important items I have learn.

Convert all your data to the same format

CONVERT(varchar(23),requested_date,101) AS requested_date,

even if

Choose a date field with values which fit into the range your desire
Call all data fields you want to join the same name.

Master Calendar Joining Mutliple Date Fields

Wed, 20 Jan 2010 18:11:07 GMT

So the above post has brought me much closer to where I want to go but is still not correct. Below is the calendar I use to create the below schema. Currently it is combining data for requested_date which is fine expect for when an event occurs when an order or printed date does not.

Ideally I would like to create a calendar with every date since 2001 and pass in order, requested and printed as variables. I have yet to find a solution like this so I am still hoping something close the below would work.


Temp:
LOAD
 min(master_date) AS minDate,
 max(master_date) AS maxDate
RESIDENT
 Backlog;
LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
LET vToday = num(today());

DROP TABLE Temp;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
 $(varMinDate) + Iterno() - 1 AS Num,
 date($(varMinDate) + Iterno() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno () -1 <=$(varMaxDate);


//*************** Master Calendar ***************
MasterCalendar:
LOAD
 TempDate AS master_date,
 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,
 inyeartodate(TempDate, $(vToday), 0) * -1 AS
 CurYTDFlag,
 inyeartodate(TempDate, $(vToday), -1) * -1 AS
 LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Master Calendar Joining Mutliple Date Fields

Wed, 20 Jan 2010 18:11:11 GMT

I do this for backlog, booked and TotalSales. The below example is just for backlog. I convert each 'X_date' into a standard. Then I change the name to 'master_date.'


Booked:
LOAD
order_date AS master_date,
qty_ordered AS qty_ordered_Ord,
qty_canceled AS qty_canceled_Ord,
qty_invoiced AS qty_invoiced_Ord,
unit_price AS unit_price_Ord,
customer_id,
ship2_name,
ship2_add1,
ship2_city,
ship2_state,
ship2_zip,
//customer_id,
order_date,
Margin_Booked;

SQL Select
CONVERT(varchar(23),order_date,101) AS order_date,

Master Calendar


Temp:
LOAD
 min(master_date) AS minDate,
 max(master_date) AS maxDate
RESIDENT
 Backlog;
LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
LET vToday = num(today());

DROP TABLE Temp;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
 $(varMinDate) + Iterno() - 1 AS Num,
 date($(varMinDate) + Iterno() - 1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno () -1 <=$(varMaxDate);


//*************** Master Calendar ***************
MasterCalendar:
LOAD
 TempDate AS master_date,
 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,
 inyeartodate(TempDate, $(vToday), 0) * -1 AS
 CurYTDFlag,
 inyeartodate(TempDate, $(vToday), -1) * -1 AS
 LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Master Calendar Joining Mutliple Date Fields

Wed, 30 Jun 2010 13:02:24 GMT

Hello Jacob,

Did you anyway solve the Problem?? I have a similar problem too..

Thanks in Advance

Sravan

Master Calendar Joining Mutliple Date Fields

Wed, 30 Jun 2010 14:08:30 GMT

Yes I did at least for us internally.

First you must convert your date into a standard format.

So something like this in the SQL load: CONVERT (varchar(23)),date_created,101) AS xdate

Then in the QV load: xdate AS master_date

This will then combine every 'date field' into the same format and join together all the 'master_dates' using QV associative nature.

The master calendar then needs to have a listing of every date between whatever time span of your choosing. This will be needed to dump your master_date into your defined time span. Then place your master_date into the temporary placeholder and use this to define your dates.

Hope this helps.

Thanks.