Master Calendar Joining Mutliple Date Fields

[#1]

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.

[#2]

deleted

[#3]

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

Important items I have learn.

1. Convert all your data to the same format

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

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

[#4]

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

[#5]

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.

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

[#6]

Hello Jacob,

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

Thanks in Advance

Sravan

[#7]

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.