Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
arixooo123
Creator III
Creator III

Data Modeling, creating a Fact from Header and Detail tables

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

7 Replies
hector_munoz
Specialist
Specialist

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,

H

arixooo123
Creator III
Creator III
Author

Dear Munoz,

Thanks for your response.

Any comment of the rest of modeling procedure?

Am I doing it right?

hector_munoz
Specialist
Specialist

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,

H

arixooo123
Creator III
Creator III
Author

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

hector_munoz
Specialist
Specialist

Of course! Send me the QVW if you want.

Regards,
H

arixooo123
Creator III
Creator III
Author

Dear Munoz,

Thank you

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

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful