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

One calendar for different date fields

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

1 Solution

Accepted Solutions
MVP
MVP

One calendar for different date fields

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
;

4 Replies
MVP
MVP

One calendar for different date fields

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

Not applicable

One calendar for different date fields

MVP
MVP

One calendar for different date fields

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
;

Not applicable

SV:One calendar for different date fields

Thank you all for helping me out!

Community Browser