topic Re: Data Model: Question in QlikView

Data Model: Question

qlikviewaf — Mon, 26 Jun 2017 14:36:52 GMT

Hi,

i created my data model which is based on the sales-table where i have the full list of sales order with related amount etc.

On my data model there's also the calendar which is linked to the invoice date of the sales-order.

Now i would like to add the budget where the dimension are: year - month - region

The region is an attribute of the customer (on sales table).

How i should create the link between the budget table and my data model? I'm a little bit confused

Thanks

Re: Data Model: Question

CarlosAMonroy — Mon, 26 Jun 2017 14:55:39 GMT

Hi Antonio,

As your budget table is also part of the Fact table (Sales), you do have to concatenate the Budget table with the Sales one.

Carlos M

Re: Data Model: Question

qlikviewaf — Mon, 26 Jun 2017 14:57:18 GMT

Why Concatenate? The budget is an information of the Sales so should not be concatenated.

Maybe a join with invoice Date + Region as KEY ?

Re: Data Model: Question

vishsaggi — Mon, 26 Jun 2017 15:06:56 GMT

Rename MonthYear fields in the Budged table and use RegionDesc to form a link between your sales and budged. OR you may want to load the Mastercalendar twice. As mentioned here by HIC:

Why You sometimes should Load a Master Table several times

Re: Data Model: Question

CarlosAMonroy — Mon, 26 Jun 2017 15:10:47 GMT

If you are going to have measures for both Sales and Budget, that's a transaction table so for a better performance you should have a single fact table link to each of its dimensions.

Best pratices documents which i have collected in the past.

Concatenate vs Link Table

One Table to Rule Them All

In this case the fact table will be Sales+Budget

and the dimension table will be Master Calendar

You will have to format the MonthYear field from the budget table like the sales one. So it both link to the MasterCalendar and that way you will avoid the synthetic key.

Re: Data Model: Question

Peter_Cammaert — Mon, 26 Jun 2017 15:33:56 GMT

The simplest solution for you would be to pull the Region_Description from your Sales table into the Master Calendar (yes, it will grow substantially but that's no problem) and add the Reqion_Description value to the keys linking Sales and Budget to your MasterCalendar. That way, your Master Calendar, which should include all periods/dates from both Sales and Budget tables, will become a link table and both Periods or Region_Description values that aren't present in either Sales or Budget will still show results from the other table..

You could also do this with a single Fact table (containing both Sales and Budget) but your Budget table has very few dimension values and making any selection in any other Sales field will throw out all Budget values. That may not be what you want.

[Edit]: a picture is worth a thousand words...

Re: Data Model: Question

qlikviewaf — Tue, 27 Jun 2017 12:59:07 GMT

Hi Carlos,

I choose to concatenate the Sales Table with the Budget Table - probably for the time being it's the most simple solution.

My question now is:

Sales is linked to the master calendar using the Invoice Date e.g 23/06/2017
Budget is made by Year/Month so how this should be managed?

Thank you for your help

Re: Data Model: Question

CarlosAMonroy — Tue, 27 Jun 2017 13:35:28 GMT

Hi Antonio,

There are many options to manage this, what comes to my mind right now, is to have the Year/Month as the same format as the sales Date. You have to pick a day to have the budget data, lets say the 1st day of each month.

So what you can do is:

In the Sales table rename the Invoice date as Date

In the budget table make the Month year field to be like Date

makedate(rigth(YearMonth,4),if(left(YearMonth,3)='Jan',1,if(left(YearMonth,3)='Feb',2,if(left(YearMonth,3)='Mar',3,

if(left(YearMonth,3)='Apr',4,if(left(YearMonth,3)='May',5,if(left(YearMonth,3)='Jun',6,if(left(YearMonth,3)='Jul',7,

if(left(YearMonth,3)='Aug',8,if(left(YearMonth,3)='Sep',9,if(left(YearMonth,3)='Oct',10,if(left(YearMonth,3)='Nov',11,

if(left(YearMonth,3)='Dec',12)))))))))))) as Date

Make sense?

Carlos M

Re: Data Model: Question

CarlosAMonroy — Tue, 27 Jun 2017 13:44:15 GMT

2nd option is to create a new composite key in the Master Calendar to be linked with the Fact table.

Fact:

//Sales

Load *, InvoiceDate&'|'&'Sales' as %KeyDate,

From Sales;

concatenate (Fact)

//Budget

Load *, MonthYear&'|'&'Budget' as %KeyDate,

From Budget;

MasterCalendar:

Load

InvoiceDate&'|'&'Sales' as %KeyDate,

Date,

MonthYear, ...

From Calendar;

concatenate (MasterCalendar)

Load

MonthYear&'|'&'Budget' as %KeyDate,

Date,

MonthYear, ...

From Calendar;

Re: Data Model: Question

qlikviewaf — Tue, 27 Jun 2017 14:28:12 GMT

Sounds good to me.

I would like to set the month-year as a date but last day of the month. How this can be done?

Thank you so much

Re: Data Model: Question

CarlosAMonroy — Tue, 27 Jun 2017 14:31:41 GMT

Just add MonthEnd() to the Date field, something like:

MonthEnd(makedate(rigth(YearMonth,4),if(left(YearMonth,3)='Jan',1,if(left(YearMonth,3)='Feb',2,if(left(YearMonth,3)='Mar',3,if(left(YearMonth,3)='Apr',4,if(left(YearMonth,3)='May',5,if(left(YearMonth,3)='Jun',6,if(left(YearMonth,3)='Jul',7,if(left(YearMonth,3)='Aug',8,if(left(YearMonth,3)='Sep',9,if(left(YearMonth,3)='Oct',10,if(left(YearMonth,3)='Nov',11,if(left(YearMonth,3)='Dec',12))))))))))))) as Date