link table for different base of time

[Anonymous]

Hi,

Perhaps someone can help me with the following:

I have a fact table for transactions on day level (containing day_id), I also have a budget table on month level (containing month_id). Finally, I have a calendar table which has amongst others, the fields day_id and month_id to join to both fact tables.

What should my link table look like to make this possible in Qlikview?

Thanks in advance.

[johnw]

It doesn't seem like you need any new tables. The three tables you described sound sufficient, and will link up automatically in QlikView. Perhaps I don't understand the question?

[Oleg_Troyansky]

I suppose you also have some other key fields in your Transactions and Budget tables - for example, Product ID, perhaps Customer Number? Or, to make things even more interesting - Transactions have Products but the Budgets have a Brand (which with an attribute of the product).

In order to link everything properly, you need to find the "lowest common denominator", - the set of fields that is common between both Transactions and Budgets. In my example, it could be something like this:

1. Month (as a representation of the Date)

2. Brand (as a representation of the Product)

3. Customer Number

The combination of the three fields would be your Link Table key. You'd need to first populate the Month and the Brand into your Transactions, in order to generate the key correctly.

If I over complicated your issue, and if, indeed, all you have is a Month and a Date, then you simply need to populate the field Month in the Transactions Table and remove it from the Calendar. This way, the 3 tables are linking nicely:

1. Transactions and Budgets are linking by Month.

2. Transactions and Calendar are linking by Date.

cheers,

[Anonymous]

Hi, first of all thanks for your replies. To clarify things, I've added a picture of the datamodel.

Remember, originally, the transactions table only has day_id, and budgets table has monthcode, both are present in the calendar table. I think I have to split the calendar table into 2 tables, one containing all fields up until the month level, and one from month level on to quarters, years, etc. What do you suggest?

[sparur]

Hi, Carlos.

What is a field "day_id" ? Is it a date in number format? Or is it only day number from 1 to 31?

If day_id it's a date so you can calculate a month from this date, such as

Month(day_id) as Month;

and then you can use this new field for linking.

[Anonymous]

Hi Anatoly,

Day_id is a technical key, but I could get the month into the transactions table by joining to the original calendar table again..

[sparur]

very strange situation.

Usually, transaction table has a date field. and you can calculate a new field "month" based on that date field. So I think that if you can create/calculate a field with month code that your problem will be resolved.