Hi All,
I have 3 tables
Cost Master:
| Origin |
Destination |
Cost |
| Chennai |
Coimbatore |
$10.00 |
| Coimbatore |
Pune |
$15.00 |
| Chennai |
Pune |
$20.00 |
| Coimbatore |
Tambaram |
$25.00 |
| Pune |
Tambaram |
$30.00 |
Movements:
| Origin |
Destination |
| Chennai |
Coimbatore |
| Coimbatore |
Pune |
| Chennai |
Pune |
| Coimbatore |
Chennai |
| Pune |
Chennai |
| Hyderabad |
Chennai |
Mapping Table:
| Location |
Alternate Location |
| Chennai |
Tambaram |
| Coimbatore |
Erode |
| Pune |
Thane |
| Hyderabad |
Secunderabad |
I need to build a result in such a way that I need to map the cost from Cost Master to Movements. But I need to consider alternate locations as well. For eg., if I don't have value for Cost between Coimbatore to Chennai, I need to use Alternate location for one of them like Coimbatore to Tambaram. I also specify them in remarks how I arrived it.
The below is the result table:
| Origin |
Destination |
Cost |
Remarks |
| Chennai |
Coimbatore |
$10.00 |
|
| Coimbatore |
Pune |
$15.00 |
|
| Chennai |
Pune |
$20.00 |
|
| Coimbatore |
Chennai |
$25.00 |
Used SPLC Tambaram Instead of Chennai |
| Pune |
Chennai |
$30.00 |
Used SPLC Tambaram Instead of Chennai |
| Hyderabad |
Chennai |
No Data |
No data found for this SPLC combination |
Please help
Thanks in advance
