Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Master Calendar Joining Mutliple Date Fields

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.

6 Replies
Not applicable

Master Calendar Joining Mutliple Date Fields

deleted

Not applicable

Master Calendar Joining Mutliple Date Fields

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.
Not applicable

Master Calendar Joining Mutliple Date Fields

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

Not applicable

Master Calendar Joining Mutliple Date Fields

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

Not applicable

Master Calendar Joining Mutliple Date Fields

Hello Jacob,

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


Thanks in Advance

Sravan

Not applicable

Master Calendar Joining Mutliple Date Fields

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.

Community Browser