2 Replies Latest reply: Jul 13, 2014 4:32 AM by Ashfaq Mohammed RSS

    Master Calendar with 3 fields



      I have main table with Dates.   (field names- id,date,car).

      I have tables with Car Numbers. (field name- carnum).


      There are some cars that has no data rows in specific years and months in the main table.

      And i need it to be in there for other joins with other data tables (expenses and so).


      To deal with it, i thought of creating a master table that will automatically create all possible year month and car rows.

      To be more specific- every car will appear in every year and month. and this master table with have another columnn

      named ID that will contain data in that format - 99920131212345 (999 + 2013 + 12 + 12345).


      afterwards i will join these tables. with ID field. and new rows would be in it and all my joins work.


      HOW to do all this i don't know.


      I'm opened to a different solution to my problem.


      Thank You.

        • Re: Master Calendar with 3 fields
          Manish Kachhia

          Create a Master Calendar with Min and Max Date...

          Now create a Temp Table with Distinct Car and do a Cartesian Join with Above Calendar Dates with All Distinct Car...


          You can Concatenate your original table with the Temp table with Where Not Exists condition.


          Now create a final table as per your requirements....


          If you still don't get the desired output, please provide us sample data file along with your required output..


          would love to help you.. 

          • Re: Master Calendar with 3 fields
            Ashfaq Mohammed



            Look at the below script.




                Date(InvoiceDate) AS InvoiceDate,

                Year(InvoiceDate) AS Year,

                'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,   

                Month(InvoiceDate) As Month,

                Day(InvoiceDate) As Day,

                Week(InvoiceDate) As Week;

            Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);


                Min(InvoiceDate) AS MinDate,

                Max(InvoiceDate) AS MaxDate

            RESIDENT Invoice;


            Also look at the below post