Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
deleted
After enough messing around. I wound back at the start. Below is the master calendar I ended up using.
Important items I have learn.
even ifCONVERT(varchar(23),requested_date,101) AS requested_date,
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
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
Hello Jacob,
Did you anyway solve the Problem?? I have a similar problem too..
Thanks in Advance
Sravan
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.