topic Help needed on data modelling in QlikView

Help needed on data modelling

Thu, 02 Feb 2012 14:43:25 GMT

I'm stuck with this case:

Table: TRANSACTIONS with link to tables CUSTOMER and ITEM

As you would expect this holds the sales data

Now I want to add the budgets. The budgets are per CUSTOMER and PRODUCTGROUP.

PRODUCTGROUP is 1 level higher then ITEM.

I've tried:

- ad the budget table and link to ITEM.PRODUCTGROUP and to CUSTOMER.ID

>> this gives a circulair reference (loop)

- concatenating the budget table to the TRANSACTIONS table

>> this gives a synthetic key as the fields PRODUCTGROUP and ITEM.ID both exist in the ITEM table as in the TRANSACTIONS table

Hope to find an idea here.

Thanks,

Johan.

Help needed on data modelling

Thu, 02 Feb 2012 14:49:00 GMT

One idea can be to create a link table and then connect Customers/Items/Budgets with Transactions using this link table.

Help needed on data modelling

kuba_michalik — Thu, 02 Feb 2012 14:50:18 GMT

One thing that comes to mind - keep the concatenation and add PRODUCTGROUP directly to the fact table (use mapping), then drop it from ITEM table. This would get rid of synthetic key.

Help needed on data modelling

Thu, 02 Feb 2012 15:35:48 GMT

Thanks for the quick responses.

Meanwhile I've concatenated the the list of PRODUCTGROUP's to the ITEM table as ITEM.ID. Now both the TRANSACTIONS as the (concatenated) budget lines link to ITEM.ID. That seams to work, just need to test the results.

But Kuba's method is probably better, when my test fails, I'm gonna try that.

I'm not familiar with Linked tables. Maybe I should 🙂

Johan