topic Re: Master Calendar with 3 fields in QlikView

Master Calendar with 3 fields

Sun, 13 Jul 2014 00:23:07 GMT

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 — Sun, 13 Jul 2014 07:57:35 GMT

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 — Sun, 13 Jul 2014 08:32:33 GMT

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

Regards

ASHFAQ