Build data model containing Bill of Material (BOM) - link table

HarryLouis — Fri, 12 Nov 2021 15:58:30 GMT

Hi all,

I have been breaking my head over this for a while now.

I have a data model that is, for the sake of simplicity, based on two separate dimensions:

product_id and rawmaterial_product_id.

I have three fact tables,

1) forecast for the product_id

2) stock levels for the rawmaterial_product_id

3) stock purchases for the rawmaterial_product_id

And two dimension tables, one for product_id details and one for rawmaterial_product_id details.

And one BOM table, containing relationship between product_id and rawmaterial_product_id and ratio need.

I have managed building a link table which accurately reflects the data in the model, but I am stuck in developing this in the front-end. The expected front-end result will be that the resulting table is something like below:

product_id	rm_product_id	ratio	product_forecast	rm_product.stock	rm_product.purchases
A	aa	100%	5	1	0
A	bb	50%	5	10	100
B	aa	200%	10	1	0
B	cc	0%	10	10	200
B	dd	1200%	10	0	50

In my existing app I can achieve this using Aggr function, but to make it more user friendly we want to implement it in the data model instead.

The problem I run into is that whenever the user selects product A or B for example, the stock levels will show 0 because the rawmaterial_product tables don't contain a reference to the product_id. Same goes for selection the raw material which makes the FC level go to 0.

Working with applymap() is not an option due to the many to many relationship. I'm thinking instead I should either join it to my dimension table or create an additional dimension table. Not even sure if I should call the BOM table a dimension table in this case.

I've read this (https://community.qlik.com/t5/Qlik-Design-Blog/Bill-of-Materials/ba-p/1462792 ) article and few others on the topic but do not see directly how they can help me.

If anyone could be of help here I would be eternally grateful :).

Thanks!

PS I have attached some sample data. I am not used to explaining these question in writing so hope my case is clear.

topic Build data model containing Bill of Material (BOM) - link table in App Development

Build data model containing Bill of Material (BOM) - link table