Relation issue

[adamjwchen]

Hi,

I have order master with 3 fields whcih related to same employee master. I don't know how to make the relationship between those two tables. I would like the person1, person2 and person3 to be replaced by proper EMMP_name. How to do it right?

  Orders:

Date	No	amount	tax	total	person1	person2	person3
9/1/2016	0901001	100	5	105	090112	090113	090114
9/2/2016	902001	200	10	210	090112	090113	090114

Employee:

Emp_ID	EMP_Name
090112	Adam
090113	Bob
090114	Unknow
090115	TEST

[vishsaggi]

Try this:

TABLE1:

CrossTable(EmpName, Emp_ID, 5)

LOAD

  *

INLINE [

Date, No, amount, tax, total, person1, person2, person3

9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

];

Employee:

LOAD * INLINE [

Emp_ID, EMP_Name

090112, Adam

090113, Bob

090114, Unknow

090115, TEST

];

[Peter_Cammaert]

The most simple one that does what you ask is like this:

MapID2Emp:

MAPPING LOAD * INLINE [

Emp_ID, EMP_Name

090112, Adam

090113, Bob

090114, Unknow

090115, TEST

];

Orders:

LOAD Date, No, amount, tax, total,

     aplymap('MapID2Emp', person1) AS person1,

     aplymap('MapID2Emp', person2) AS person2,

     aplymap('MapID2Emp', person3) AS person3

INLINE [

Date, No, amount, tax, total, person1, person2, person3

9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

];

You can also create an interface table between Orders and Emplkoyees using a CROSSTABLE LOAD. That way you won't duplicate critical values like amounts. For example:

Orders:

LOAD *

INLINE [

Date, No, amount, tax, total, person1, person2, person3

9/1/2016, 901001, 100, 5, 105, 90112, 90113, 90114

9/2/2016, 902001, 200, 10, 210, 90112, 90113, 90114

];

Order2Emp:

CROSSTABLE (Emp_SeqNo, Emp_ID, 1)

LOAD No, person1, person2, person3

RESIDENT Orders;

Employee:

LOAD * INLINE [

Emp_ID, EMP_Name

090112, Adam

090113, Bob

090114, Unknow

090115, TEST

];

Best,

Peter