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