Data Modelling Concepts - Many-to-Many Relationship Qvd Generation

[#1]

Hi All

When I started my journey with Qlikview 7 months back, I was not very strong in Data Modeling and other related concepts.

But slowly and steadily I have learnt the concepts and technicalities through Qlikview community (Thanks to everyone for that)

and by learning myself. But one thing that always bothered and challenged me is handling Many-to-Many, Many-to-one

and one-to-many relationships.

To use Left Join or Right is very easy between one-to-one Relationships. Use Left Join Between 2 tables and it will create a Qvd for you if you have written Store Command in the end.

Now the Pain Area is to handle Multiple Data, I mean Many-to-Many, One-to-Many and Many-to-One Relationships.

If I use Left Join between 2 tables which have multiple Data against single record than it will multiply the Data if I use Sum or Count.

Also, increases the Data Volume.

I know this thing can easily be solved by using Link Model Approach or by just Linking the 2 Tables using Key.

But, If I want the Data in one Table or One qvd than i have to use Join depending upon the scenario - may be Left Join,

Right Join or other Joins but that will multiply Data.

I think, everyone face this issue.

So, how to handle such scenarios?

Need Suggestions.

ApplyMap() Function will not work here as it will pick only the First Value.

Also, I have attached a QVW. I am trying on the solution but no luck.

Any help would be Appreciated.

Regards

Eric

[jonathandienst]

There's no single answer to something like this, the range of possible scenarios is very broad, and there are different requirements depending on the scenario.

Adding new rows with a many to many or one to many is not necessarily an error. Consider for example, loading sales order headers (one line per order) and then joining the sales detail (many per order), adding many records per order.

And not all data models can usefully be collapsed into a single table - consider models requiring link tables or a canonical link tables.

So the answer depends on what you are trying to achieve in the data model.

[#3]

Thanks Sir for you Reply.

But suppose, we have to derive some field Table1 and Table2 than Link approach will not work.

eg.

AAA:

Load * inline [

Key, Value

1111, 100

1111, 300

1112, 400

1113, 200

1113, 400

];

Left JOIN(AAA)

Load * inline [

Key, NewAmount

1111, 1000

1112, 4000

1113, 2000

];

Now, I have to use NewAmount Field to generate new Field and so on...

So, this type of thing is not possible in Link Approach.

Also, I don't want to handle such cases at Front end.

Regards

Eric