How to import multiple recursive data tables

[Obsyky]

Hello,

In my opinion, I'm faced with a rather complex problem 😕
I have tables that are all linked together, but which can be recursive on their own.
In the simplest sense, I have an ingredient that is linked to a product, which in turn is linked to a section in a shop.
But we can make things even more complex by saying that to make a first ingredient I need two others, which form a product that I'm going to use to make a second one, this product is going to be in a sectio,, which itself is in another section, this section is attached to a shop, a shop which is attached to another shop.

I tried using the Hierarchy function. The problem is that from column 2 I can get different types of information

And I would have liked to have had separate information. For example, if I select a shop, I see everything that's attached underneath, and if I say that I want the ingredient type to be "wheat", for example, I get a list of products containing wheat.
Or if I filter on a product, it gives me all the by-products and ingredients I need. Etc.

Bearing in mind that each type of data does not necessarily have the same attributes.

One idea I've come up with is to calculate the maximum number of links there are, and add "empty" links to ensure that everything is arranged in the same column

If you have any ideas on how to do this or how to improve on what I've already been able to do, please let me know. Thank you very much !

[WaltShpuntoff]

Why must it be a hierarchy? It sounds like you are more interested in the relationships.

I don't know how big your dataset you may want to explore something like this.

• Create an attribute list
  • Type
    • Ingredient
    • Product
    • Section
  • Value
  • RecordID
• Create your "container" records which would be what you normally have at the top of your hierarchy. 
• Have a link table that goes from your container to your attribute

HTH

-ws

[Obsyky]

I started with this idea because the data is structured according to a hierarchical logic.

With your proposal, how can I go from an ingredient to everything it's linked to?

[WaltShpuntoff]

Thinking out-loud as it were.

Have tables for

• Ingredient
• Subproduct
• product
• store

Now let's go ugly

Link tables (yes, plural)

• Ingredient to Product / subproduct Link
• Product to Section?
• Section to store?

Or you could go down a different path and look into Generic Loads.

Link to blog post on Generic Load

You can always create a mock dataset for us all to play with to see what comes of it.

hth

-ws

[Obsyky]

I'm going to create a test dataset, because I think it will be understandable 😉

Because in your example, an ingredient is directly linked to a product / by-product, except that I have cases where before finding the link between the ingredient and its product, I have 10 ingredients between the two 😕

[Obsyky]

Any ideas?
Even a partial one. Maybe this will help me find a more global solution in the back of my mind 🙂

[marcus_sommer · Accepted solution]

Without diving deeper into the challenge I would not tend to classical link-table approaches else more to combine several data-models into a single one respectively to concatenate them. This means to specialize certain sub-sets for certain views because I have some doubts that a "normal" data-model could fulfill all requirements - especially to aspects like the development resources, maintainability and performance - because a general working application has not much sense if it's too slow. You may consider it as a division of work which will simplify each single step and afterwards combining them again.

By concatenating is something like this meant: Fact Table with Mixed Granularity - Qlik Community - 1468238 whereby it mustn't be mandatory a mixed granularity else also just different views unified in a single fact-table + n source-fields to differentiate between them in dimensions/selections/conditions.

A similar approach might be also applied by the dimensions - also concatenated. Quite useful in this regard might be also a special kind of dimension-table: The As-Of Table - Qlik Community - 1466130. And this might be also applied of any kind of Hierarchies - Qlik Community - 1487801.

[barnabyd]

G'day @Obsyky

Have you seen this post by Henric? I read it ages ago and I think it addresses your issue.

Cheers, Barnaby.