topic One calendar for different date fields in QlikView

One calendar for different date fields

Tue, 04 Jan 2011 12:00:06 GMT

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?

One calendar for different date fields

stevedark — Tue, 04 Jan 2011 12:21:13 GMT

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

One calendar for different date fields

Tue, 04 Jan 2011 12:22:34 GMT

Hi ,

Please refer the link.

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

One calendar for different date fields

johnw — Wed, 05 Jan 2011 01:58:27 GMT

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
;

SV:One calendar for different date fields

Wed, 12 Jan 2011 15:45:32 GMT

Thank you all for helping me out!