Data from one table to another

Bharathi09 — Wed, 01 Mar 2023 12:12:44 GMT

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

Re: Data from one table to another

justISO — Fri, 03 Mar 2023 07:59:25 GMT

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;

topic Data from one table to another in App Development

Data from one table to another

Re: Data from one table to another