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

    Master Calendar with 3 fields

      Hi.

       

      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

            Hi,

             

            Look at the below script.

             

            CalendarMaster:

            LOAD

                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);

            Load

                Min(InvoiceDate) AS MinDate,

                Max(InvoiceDate) AS MaxDate

            RESIDENT Invoice;

             

            Also look at the below post

            http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

             

             

            Regards

            ASHFAQ