Modeling circular dat

[leenlart]

Hello, 

I'm creating a Qlik application for quering data from our PLM.  

I have a circular scenario that I can't figure how to model, and I'd really appreciate any help!

I have an Items table, this is the items sold to customers, with all their various info from the PLM, including an ingrediants list.  I load everything except the ingrediants to the Items Table.  I then load the Ingrediants list as a table, with fields : ItemCode, IngrediantCode.   

Then I load in the RawMaterial into a components table, which links to the IngrediantsTable by IngrediantCode.

Then I load in the Packaging also into the components table, which links to the IngrediantsTable by IngrediantCode.  

Next comes the recipes, because of course why should the ingrediants be simple if our company can make things difficult ?  In the PLM, a final Item's ingrediants can be either Raw materials, packaging or a recipe.  A Recipe also includes an ingrediants list, that can be made up of Raw materials, packaging or a different recipe.  

And this is where things are tricky.  How can I load the data to be able to get a complet vision of the packaging, for exemple, that goes into a final product ?  The packaging can be in the ingrediants list directly on the final product, it can be in a recipe in the ingrediants list, or it can be in a sub-recipe of the recipe in the ingrediants list.  

How would you model this data ?  

Thanks for any ideas!

PS, not sure it matters here, but I'm using Qlik Sense Enterprise, we're on Nov 2025.  

[marcus_sommer]

I would merge the tables in the direction of a star-scheme data-model - means a single fact-table + n dimension-tables. The attempt to transfer a relational table-scheme from an ERP system near to 1:1 to a BI tool is usually not useful because the needed efforts and complexity would be much higher as building a suitable data-model just from the beginning. 

[rubenmarin]

Hi, this looks like an scenario I found some time ago, I don't remember the details but the solution was to iterate the recipe table to decompose all the recipes into it's components: First load the recipes that doesn't have any recipe on it's components, having these merge the components and the recipes that has another recipe as component to decompose in it's components. We had to iterate many times because a recipe could have a recipe that was part of another recipe, that was part of another... so a "do...loop while/until" until the final table doesn't has any component with a recipe that wasn't decomposed

So at the end you finish with a table that only list a recibe and it's components, merge this table with the ingredients and the items table, and you have a recipes table with all the info you need.

The tricky part was to calculate the volume of each ingredient, because you can have a recipe to create 100ml of an item, and this recipe is used on another that uses 30ml of that recipe, that to create 100ml of that recipe already uses 40ml of a third recipe, and this one has the final ingredients to create 100ml of this this third recipe, like 5ml of ingredient1, 7ml of ingredient2..., so you will need to apply the percentajes to bring the correpondent volumes as if they were part of the many recipe. (5ml * 40% * 30% = 0,6 ml in the main recipe).

Look easier if you have all recipes that creates 100 ml, but there were many different volumes (330ml, 1000ml) and measure units like grams, units, hours... but at the end it's just to apply the percentages to each component to bring it to the main recipe.