Confusion in structure, tMap

Question

I have a dimension table called Product_Dim

Create Table Product_Dim
(
Product_ID INT primary key,
Product_Line CHAR(20),
Product_Category CHAR(25),
Product_Group CHAR(25),
Product_Name CHAR(45),
Supplier_Country CHAR(2),
Supplier_Name CHAR(30),
Supplier_ID INT
)

and two source tables Supplier and Product_List

Product_list is as follow :

Product_ID
Product_Name
Supplier_ID
Product_Level
Product_Ref_ID

where Product_Level has the values (1,2,3,4)

1 means it is a product 2 means it is a product group 3 means it is a product category 4 means it is a product line

so in order to fill my Product_Dim table I did drag 4 times Product_list (Changing the query accordingly to Product_level) and one supplier

so for the join i do the following :

 product_list.Product_Name  => PRoduct_Name
 product_group.Product_Name  => PRoduct_Group
 product_line.Product_Name  => PRoduct_line
 product_group.Product_category  => PRoduct_category
 ....

My problem is in the product_ID

I don't know what to drag in there, I thought on dragging the four Product_IDs but I don't know what expression to write.

Any help will be appreciate it.

EDIT : Product_Dim

 Product_ID Product_Name Product_line Product_group ...
 1          PN1
 2          PN2
            ....
 3                       PL1
 4                       PL2
                         ....
 5                                     PG1
 6                                     PG2
                                       ...
 ....

and so on, but i dont know how to do this.

Answer 1

OK, it was hard but I get your problem.

Well the first solution that comes to my mind, is to split that in 4 tMap components.

This should solve your problem.