Master Calendar with 3 fields

[#1]

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.

[MK_QSL]

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.. 

[ashfaq_haseeb]

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