Data from one table to another

[Bharathi09]

Hi all,

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. 

The below is the result table:

Result Table
Origin	Destination	Cost	Cost Origin	Cost Destination
Chennai	Coimbatore	$10.00	Chennai	Coimbatore
Coimbatore	Pune	$15.00	Coimbatore	Pune
Chennai	Pune	$20.00	Chennai	Pune
Coimbatore	Chennai	$25.00	Coimbatore	Tambaram
Pune	Chennai	$30.00	Pune	Tambaram
Hyderabad	Chennai	No Data	Hyderabad	Chennai

Please help

Thanks in advance

[justISO]

Hi, not so elegant solution, but should work:

Cost_master:
load * inline [
Origin, Destination, Cost
Chennai, Coimbatore, 10
Coimbatore, Pune, 15
Chennai, Pune, 20
Coimbatore, Tambaram, 25
Pune, Tambaram, 30];

Movements:
load * inline [
Origin2, Destination2
Chennai, Coimbatore
Coimbatore, Pune
Chennai, Pune
Coimbatore, Chennai
Pune, Chennai
Hyderabad, Chennai];

mapping:
load * inline [
Location, Alternate Location
Chennai, Tambaram
Coimbatore, Erode
Pune, Thane
Hyderabad, Secunderabad];

//------

NoConcatenate
temp:
Load 
Origin2 as Origin,
Destination2 as Destination
Resident Movements;

left join (temp)
load
Location as Destination,
[Alternate Location]
Resident mapping;

left join (temp) //fill cost
load
Origin, 
Destination,
Cost,
Origin as [Cost Origin],
Destination as [Cost Destination]
Resident Cost_master;

left join (temp) //fill cost for alternative
load
Origin, 
Destination as [Alternate Location], 
Cost as Cost2,
Origin as [Cost Origin2],
Destination as [Cost Destination2]
Resident Cost_master;

drop tables Cost_master, Movements, mapping;

NoConcatenate
main: //combine everything to final table
load
Origin,
Destination,
Coalesce(Cost, Cost2, 'No Data') as Cost,
Coalesce([Cost Origin], [Cost Origin2], Origin) as [Cost Origin],
Coalesce([Cost Destination], [Cost Destination2], Destination) as [Cost Destination]
Resident temp;

drop table temp;