Multiple fields with same name and same values but different origins

[Saryk]

Hello,

I load data from multiple sources. Let's say I have 2 excel files : "Sold" and "Bought".

I have an item ID that is my main key common to all my tables, but I also have the department that is making the transaction for example, so they are called "buying_department" and "selling_department", and they both have dept1, dept2 and dept3 as values.

When I do my graphs, I have one for sales where I can filter on "selling_department" and one for buying where I can filter on "buying_department" ; but I'd like to have one filter on department that can carry over from one sheet to the other.

How can I manage this in my data model without having synthetic keys ?

I don't think hashing is viable either, because I simplified this explanation but the reality is I have several tables and several fields. 

Any help appreciated, thank you !

[Lisa_P]

I would recommend that you load the data with a common field Department and add a new field in each table to differentiate between Buying and Selling or bought and sold.

eg

Load ...
           buying_department as Department,
           'Bought' as DeptType .....

This way they will associate if that is what you are trying to do.
     

[TomKogut]

Hi @Saryk ,

Another usefull approach would be to contatenating the "Sold" and "Bought" tables giving a commom name to the field department in both tables. Then creating a dimension "Department" linking to this table.

Table_1:
Load buying_department AS Department,
     'Sold' AS Origin,
     ...
Resident [Sold];
Concatenate
Load selling_department AS Department,
     'Bought' AS Origin,
     ...
Resident [Bought];

Dimension_1:
Load Department
Resident [Department];

Drop Table [Sold], [Bought]; 

When you select an department it will show both buying and selling department.

Please, check as usefull if it helps you.

-Tom

[Saryk]

Wait, you mean you can load a field as a table ?

[TomKogut]

Hi @Saryk ,

no, sorry, I thought you have a table (dimension) for your Department, but if you don't, you are not obligated to, it's just a project choice.

-Tom

[Saryk]

Hey,

I'm kind of new to Qlik and not used to all the practices and data modelling ; what would be the purpose of having such a table ?

[TomKogut]

Hi @Saryk ,

We have mostly two kinds of tables, dimensions and facts.

• A table with numeric fields (measurements) is called fact table, a row in a Fact table is a Fact (something who happened). In your case, a "Sold" and "Bought" are Fact tables and would have fields like Amount ($), Price, Quantity...
• A table with description fields is called a dimension table.

We use to say that a dimension describes a fact. But it's always a project decision. In your example, department would just be a dimension table, just if you have more fields about the department, If you have just the field department, I think you should put this field directly inside the Fact table (who is called degenerate dimension), see the below example:

Load [Department],
     [Description department],
     [Phone],
     [Responsible],
     ...
Resident [X];

 See this thread: Dimensions vs Facts 

-Tom