1 Reply Latest reply: Nov 15, 2017 12:02 PM by omar bensalem RSS

    Match of Year of Sales table and Year of Target table

    Gilad Magori

      Hi,

      I have a fact table of sales,

      It has an OrderDate field which connect to Master Calendar table.

      I have also a TargetCategory table as below;

      Target table.PNG

      u can see below the relevnt part of the data model viewer,

      viewer.PNG

      i made this screen, and my question is how choosing of year will choose also the correct year of target(same solution for Month)?

       

      SCREEN.PNG

      Thanks,

      Gilad

        • Re: Match of Year of Sales table and Year of Target table
          omar bensalem

          It would be sthing like this:

           

          here's a script you can use to build a test application and understand how to handle different dates:

          SET DateFormat='YYYY-M-D';

           

           

          Table1:

          LOAD * INLINE [

          ID, Seq, ActivityDate, Value1

          1, 1, 2011-5-10,10

          1, 2, 2011-5-12,2

          2, 1, 2011-5-12,20

          3, 1, 2011-6-15,40

          ];

           

           

          Table2:

          LOAD recno() as ID, * INLINE [

          DepartureDate, ArrivalDate, Value2

          2011-6-15,2011-6-16,5

          2011-6-15,2011-6-17,10

          2011-6-16,2011-6-16,20

          ];

           

           

          OtherTable:

          LOAD recno() as OtherID, * INLINE [

          OtherDate, OtherValue

          2011-5-12,2

          2011-6-15,3

          2011-6-17,5

          ];

           

           

           

           

          Link:

          LOAD

          ID

          ,Seq

          ,ActivityDate as Date

          ,'Activity' as DateType

          RESIDENT Table1

          ;

          CONCATENATE (Link)

          LOAD

          ID

          ,DepartureDate as Date

          ,'Departure' as DateType

          RESIDENT Table2

          ;

          CONCATENATE (Link)

          LOAD

          ID

          ,ArrivalDate as Date

          ,'Arrival' as DateType

          RESIDENT Table2

          ;

          CONCATENATE (Link)

          LOAD

          OtherID

          ,OtherDate as Date

          ,'Other' as DateType

          RESIDENT OtherTable

          ;

           

           

          Calendar:

          LOAD *

          ,date(monthstart(Date),'MMM YYYY') as Month

          ;

          LOAD date(makedate(2011,5,1)+recno()-1) as Date

          AUTOGENERATE 61

          ;

           

           

           

          Capture.PNG

          Capture.PNG

           

          credits to johnw