topic Re: Data Modeling, creating a Fact from Header and Detail tables in QlikView

Data Modeling, creating a Fact from Header and Detail tables

arixooo123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys

I am a bit new to the modeling in qlick, I have already done some research about creating a star schema from a relational DB.

I have done these steps so far:

1- Connected to DB

2-Selected some tables and columns

3-Made sure each table has a primary key

4-Removed all relationships between tables by renaming foreign keys

5-Created a link table by concatenating primary keys of Dim Table (Which I am not sure whether I did the right thing or not)

6-Joined the sales Header and Detail tables to create a Fact table.

Now, the problem is some values in Header table have different granularity than Detail's, for example Cost. How can I divide the amount proportionally between lines of detail table.

ShipDim:

Load

ShipOrderID,

ShipOrderDetailID,

ShipMethod,

ShipDate

Resident ShipDetail;

left join load

ShipOrderID,

RegDate,

Status,

Tax,

Cost

Resident ShipHeader;

Would you please let me know your opinion on the whole design and let me know how I can make it right?

Thank you so much

Re: Data Modeling, creating a Fact from Header and Detail tables

hector_munoz — Fri, 24 Mar 2017 04:34:55 GMT

Hi Ar,

A quick trick is to add the number of records (ShipOrderDetailID) that a ShipOrderID has. You can do:

LEFT JOIN (ShipDim)

LOAD ShipOrderID,

Count(ShipOrderDetailID) AS [# ShipOrderDetails],

RESIDENT ShipDetail

GROUP BY ShipOrderID;

In the expressions you can divide Cost / [# ShipOrderDetails].

Anyway, maybe you should not join into one if this could mean future problems...

Regards,

Re: Data Modeling, creating a Fact from Header and Detail tables

arixooo123 — Fri, 24 Mar 2017 04:40:12 GMT

Dear Munoz,

Thanks for your response.

Any comment of the rest of modeling procedure?

Am I doing it right?

Re: Data Modeling, creating a Fact from Header and Detail tables

hector_munoz — Fri, 24 Mar 2017 04:44:40 GMT

Hi,

As you said, you are new in Qlik but you are creating link tables, asking in the forum, guessing how to improve things... I think the answer is yes! 😉

Regards,

Re: Data Modeling, creating a Fact from Header and Detail tables

arixooo123 — Fri, 24 Mar 2017 06:42:17 GMT

Dear Munoz

I guess there's a problem in my model.

I created the link table only by concatenation, added the primary keys of all dimension tables to link table but the columns have no relationship. I guess I should have joined the tables instead, Right?

Besides, I am getting an error while executing the expression you shared. would you please accept my follow request so I can send you the logs there?

Thank you so much

Re: Data Modeling, creating a Fact from Header and Detail tables

hector_munoz — Fri, 24 Mar 2017 07:41:29 GMT

Of course! Send me the QVW if you want.

Regards,
H

Re: Data Modeling, creating a Fact from Header and Detail tables

arixooo123 — Fri, 24 Mar 2017 07:48:08 GMT

Dear Munoz,

Thank you

Is it possible for you to give me your email address?

Re: Data Modeling, creating a Fact from Header and Detail tables

Anil_Babu_Samineni — Sat, 25 Mar 2017 16:32:20 GMT

May be look here. Link table is nothing but all similar fields created the there own table while using Link table. PFA