4 Replies Latest reply: Jan 12, 2011 10:45 AM by adbmita RSS

    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

        • One calendar for different date fields
          Steve Dark

          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
            John Witherspoon

            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

              Thank you all for helping me out!