Data Model difficulty

[#1]

Hi all,

I am having some issues with properly setting up my data model. The dashboard is about Engineering Change Notices where the business changes from one old part to another new improved part.

In section 2 below I have a table for Parts (‘Parts’) and a number of other tables with information relating to the parts such as Schedules on the Part, Demands on the Parts, Stock On-Hand on the part etc.

In section 1 I have a number of tables relating to the Engineering Change Notices (ECN). Each ECN has two parts related to it, the Old Part and the New Part. Both the old and the new part are found in the ‘Parts’ table.

I am finding a problem to related (1) in the data structure below with (2) in such a way that I can get all information (Schedules, Demand, Stock on-hand, etc.) relating to both the old part and the new part of an ECN.

Does anyone have some recommendations regarding how I can tackle this situation?

[Chanty4u]

hi,

Re: Removing Synthetic Keys

Synthetic Keys

[#3]

Hi,

Can you please elaborate further such as what issues this is causing to this particular problem?

I don't think the synthetic key is the issue here and I am using it in the correct way to related schedule, demand etc. with the calendar.

Ths

[Chanty4u]

chk dis

Link Table in QlikView | Learn QlikView

[jonathandienst]

Are you asking for advice on how to structure the data model? I think it can be simplified and I doubt that the synthetic key is a problem in this structure, but it might not be necessary.

Its hard to be really specific without access to a representative sample, but I would consider concatenating the old & new part tables into single table (with an Old/New derived key; joining the stock tables into a single stock table and combining the MRP tables in some way for a start.

You will need some association between Section 1 and 2  - how are they associated?

[vikasmahajan]

It Seems PartID & YearWeek are common , create key like this PartID&'-'&YearWeek  as PartYrKey  while loading tables.

HTH

Vikas

[#7]

I don't think the synthetic key had anything to do with my problem.

But if I create a key like PartID&'-'&YearWeek how can I then relate:

1. the PartID in 'MRP Schedule', 'MRP Demand' and 'ASNs' with the parts in other tables.

2. the YearWeek in 'MRP Schedule', 'MRP Demand' and 'ASNs' with the yearweek in the 'Time' table

?

Thanks

[vikasmahajan]

you need to create a key all this 3 tables so that they will link automatically in Qlik.

Vikas