topic Re: Master Calendar in QlikView

Master Calendar

Fri, 25 Apr 2014 19:55:37 GMT

If I have a "Sales Date" and a "Goal Date" is there anyway to have these dates combined into one master table even if they come from different source data? I was able to create a master calendar for Sale & Goal, but need to combine thses some how. Any help would be greatly appreciated.

Re: Master Calendar

its_anandrjs — Fri, 25 Apr 2014 20:17:36 GMT

Yes but you need to create same name field in both the tables and connect it with the master calendar.

As you say you have already Master calendar based on the Sales and Goal table then you have to use your table like below script

Eg:-

SalesTable:

Load

"Sales Date"

"Sales Date" as CommonDate

From Location;

GoalTable:

Load

"Goal Date"

"Goal Date" as CommonDate

From Location;

//Master Calendar based on the Sales and Goal Table and which is based on the Sales and Goal dates

MasterCalendar:

Load

CommonDate

From Location;

Re: Master Calendar

MK_QSL — Sat, 26 Apr 2014 08:35:24 GMT

Try below in your script...

=====================

Orders:

Load

OrderNumber,

ConfirmationDate,

DeliveryDate,

Value

Inline

[

OrderNumber, ConfirmationDate, DeliveryDate, Value

760864, 06/01/2014, 16/01/2014, 200

760865, 06/01/2014, 16/01/2014, 120

760866, 06/01/2014, 20/02/2014, 300

760867, 06/01/2014, 20/02/2014, 400

760868, 06/01/2014, 25/03/2014, 250

760869, 06/01/2014, 27/03/2014, 180

760870, 01/02/2014, 12/04/2014, 600

760871, 06/02/2014, 12/04/2014, 300

760872, 06/02/2014, 06/06/2014, 200

760873, 16/03/2014, 06/05/2014, 180

760874, 16/03/2014, 06/06/2014, 150

760875, 16/03/2014, 06/06/2014, 250

760876, 26/03/2014, 06/06/2014, 210

760877, 26/04/2014, 15/05/2014, 230

760878, 26/04/2014, 25/05/2014, 400

760879, 16/05/2014, 30/05/2014, 230

760880, 16/05/2014, 01/06/2014, 90

760881, 16/06/2014, 18/06/2014, 100

760882, 16/06/2014, 26/06/2014, 120

];

OrderConfirm:

Mapping Load

OrderNumber,

ConfirmationDate

Resident Orders;

OrderDelivery:

Mapping Load

OrderNumber,

DeliveryDate

Resident Orders;

NoConcatenate

Final:

Load

OrderNumber,

ApplyMap('OrderConfirm',OrderNumber,Null()) as Date,

'Order' as DataType,

1 as CountFlag,

Value

Resident Orders;

Load

OrderNumber,

ApplyMap('OrderDelivery',OrderNumber,Null()) as Date,

'Delivery' as DataType,

-1 as CountFlag,

Value

Resident Orders;

//Canonical Date Calendar

MinMaxDate:

Load

Date(Min(Date)) as MinDate,

Date(Max(Date)) as MaxDate

Resident Final;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MaxMaxDate'));

TempCalendar:

Load

Date($(vMinDate) + RowNo() - 1) as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

MasterCalendar:

Load

TempDate as Date,

Month(TempDate) as Month,

Year(TempDate) as Year

Resident TempCalendar

Order By TempDate Asc;

Drop Tables Orders, MinMaxDate, TempCalendar;

Re: Master Calendar

SunilChauhan — Sat, 26 Apr 2014 08:43:54 GMT

Eg:-

Load

[Sales Date]

[Sales Date] as CommonDate

From yourDBPath;

Load

[Goal Date]

[Goal Date] as CommonDate

From yourDBPath;

MasterCalendar:

Load

[Sales Date] as CommonDate

From yourDBPath;

Concatenate(MasterCalendar) ////this is important step

Load

[Goal Date] as CommonDate

From yourDBPath;

hope this helps