One calendar for different date fields

[#1]

Hi all,

I need your help.

I have database with different date fields which I need to connect to ONE calendar in my QV application. I've created an autogenerated master calendar but how do I link the different date fields to it?

For an example, if I click on March 3, 2010, I want records of orders which was made on that day (OrderDate), orders that was packed that day (PackingDate), and orders which was shipped that day (ShippingDate).

I don't want several different calendars for the different date fields, and I've heard that one can do this with ONE calendar....

Any advice?

Mi

[stevedark]

Hi Mi,

The best way of achieving this is to have a single fact table with all tables that contain a date concatenated into it. The date across all the tables will need to be in the same column of the concatenated table. Any other common fields between tables should be in one column in the new table. Any columns that don't exist in all tables should be populated with null() or zero when they are concatenated in.

If you go this route then you will need to be aware of how the change will affect joins and selections.

A basic example of the load script would be:

FactTable:
LOAD
SalesDate as Date,
CustomerNo,
null() as SupplierNo,
ItemNo,
SalesAmount,
0 as PurchaseAmount
FROM Sales.xls ....;

FactTable:
LOAD
PurchaseDate as Date,
null() as CustomerNo,
SupplierNo,
ItemNo,
0 as SalesAmount,
PurchaseAmount
FROM Purchases.xls ....;

Note that the columns loaded from both sources are identical to cause an implicit concatenate. A forced concatenate can be done by using the Concatenate statement.

Hope that helps - please post back if you want further info.

Regards,
Steve

[#3]

Hi ,

Please refer the link.

http://community.qlik.com/forums/t/38812.aspx

[johnw · Accepted solution]

Here's one way.

OrderActivities:
LOAD
OrderID
,'Ordered' as Activity
,OrderDate as Date
RESIDENT Orders
;
CONCATENATE (OrderActivities)
LOAD
OrderID
,'Packaged' as Activity
,PackagingDate as Date
RESIDENT Orders
;
CONCATENATE (OrderActivities)
LOAD
OrderID
,'Shipped' as Activity
,ShippingDate as Date
RESIDENT Orders
;
// Optional: It can be useful to still have the original fields.
DROP FIELDS
OrderDate
,PackagingDate
,ShippingDate
;

[#5]

Thank you all for helping me out!