Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

2 Replies
MVP
MVP

Re: Master Calendar with 3 fields

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

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

Community Browser