Data Model

[melissayzy]

Hi,

I have some questions over my data model.

I have 6 data tables A-F and I want to do the following three logical joins among them. 

1. A inner join B outer join C outer join D

2. B inner join E outer join A

3. B inner join F outer join A

Each of these three is a separate view of my data. So far, I have loaded A-F tables individually and am wondering what should I do next? I have tried to use residential load for each of these three but there are lots of synthetic keys. i want to avoid synthetic keys. Can someone give advice? Thank you.

[vamsee]

Hi,

Data modelling is highly dependent on the granularity of data in each of the tables and the primary keys associated to join the tables. Stating that, I personally feel the ideal way to create a perfect data model is by visualizing the charts to be displayed on the UI. 

Coming to your question, you might need to provide some sample data or columns names for someone to give you a better picture. 

According to my understanding, check if you need all the columns from the second table. Drop the fields which are not needed.

Or

Instead of using inner joins try creating flags using applymaps.(Applymap) You can use these flags in specific charts to filter down.

Another option is to test using Qualify

[rubenmarin]

Aditionally to Vamsee tips I would add that if tables E and F only are seen as an inner join of B you can do the inner join in script, this way you have less tables wich will be a less complex model.

For each visualization only use the fields that comes from the tables you want to show.

[Anonymous]

As per my understanding you might get this from SQL view or Store Procedure, here we have to understand what is the business logic for the current requirement. If your Joins are given requirement check as below.

First don't load all the tables at a time, go one by one as your requirement and drop unwanted.

1. A inner join B outer join C outer join D-- first load A Inner Join B-> Output is A Join D=  output is 'A' table rename it as X.

Second logic

2. B inner join E outer join A --> Load B Inner Join E tables=Output is B Join A= Output is B table rename it as Y.

Third Logic

3. B inner join F outer join A-> Load B Inner Join F tables=Output table is B Join A= Output tables is B rename it as Z.

Finally, you X,Y,Z tables now find how to merge these tables or keep individually its depend on your requirement.

I hope that have answered your question.

Regards

Pavan Nallani

[marksouzacosta]

Hi,

We just released a new video in our YouTube channel related to your question.
I hope it helps!

IPC Tips - Removing Synthetic Keys
https://youtu.be/tETUxntiqew

Regards,

Mark Costa

[Brett_Bleess]

Only other thing I can add to this one is the following Help link:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/best-p...

If someone's answer got you what you needed, we would appreciate it if you could close out the thread by using the Accept as Solution button on that post(s) to give credit for the help and let other Members know what worked.

If you need further help, please leave an update post, and if you figured out something else, please consider posting that and then use the button to mark that post...

Regards,
Brett