Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
];
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