2 Replies Latest reply: Oct 5, 2012 7:09 AM by Henric Cronström RSS

    Joining Master Calender to two or more tables on Date field

    Kiran Shinde

      Hi Everyone,

       

      Below is my requirement

       

      TableOrder NoItem No
      Order Date
      GRN Date
      Purchase Order (PO)PPP
      Purchase Reciept (PR)PPPP
      Master Calender

      PP

       

      I have to connect Master Calendar on Date field to Purchase Order Order Date field & Purchase Receipt GRN Date Field.

      Order Date id Present in PO & PR.

      So that,when user select Any perticular Month-Year,he will get all the Orders generated in that selected month as well as Orders whose GRN generated in that month.

       

      How can I do this?

       

      Thanks

        • Re: Joining Master Calender to two or more tables on Date field
          Kamal Naithani

          Hi,

          Kiran may be this can help you

           

          Calendar:
          LET vDateMin = Num(MakeDate(2010,1,1)); 
          LET vDateMax = Num(MakeDate(2011,12,31));  
          LET vDateToday = Num(Today()); 

          TempCalendar: 
          LOAD
          $(vDateMin) + RowNo() - 1 AS DateNumber, 
          Date($(vDateMin) + RowNo() - 1) AS TempDate 
          AUTOGENERATE 1 
          WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

          Calendar: 
          LOAD
          Date(TempDate) AS CalendarDate, 
          Month(TempDate) AS CalendarMonthName, 
          Year(TempDate) AS CalendarYear
          RESIDENT TempCalendar ORDER BY TempDate ASC;

          Link:
          LOAD

          Order,
          OrderNo,etc
          Order DATE as CalendarDate,
          'OrderDate' as DateTypeFlag
          RESIDENT Purcahse Order;
          concatenate (Link)
          LOAD

          GRC No,

          etc

          Purchase Date as CalendarDate,
          'PurcaseDate' as DateTypePurcase
          RESIDENT [Purcse Reciept];

          DROP TABLE TempCalendar;

           

           

          Hope it helps you.

          Regards

          Kamal


          • Re: Joining Master Calender to two or more tables on Date field
            Henric Cronström

            A link table or a bridge table can connect one single master calendar to several dates. But why do you want to do that? If you do that you cannot make selections like "Find transactions that have order dates in Oct and GRN dates in November". It's much better to load several calendar tables.

             

            See more on the blog about multiple calendar tables.

             

            HIC